﻿'Description:排产下发、回收后台方法
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Zhangrj 
'create Date:20220518
'Rewriter:
'Rewrite Date:
Imports System.Text
Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports DatabaseAccess.IDAL
Imports DatabaseAccess.DALFactory
Imports System.Data.OracleClient
Imports BusinessRule.Common

Public Class ApsPlanDownSendBusiness

    Private rbFactoryID = System.Configuration.ConfigurationManager.AppSettings("RBFactoryID").ToString() '热表ID

    ''' <summary>
    ''' 获取排产下发列表数据（分页）
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetApsPlanListByPage(ByVal para As Dictionary(Of String, String)) As uMESProcessResult

        Dim strSql = <string>SELECT apd.apsplandetailid, cci.mfgorderid,cci.mfgordername,cci.containerid,cci.containername,cci.productid, cci.productdescription description,
cci.productname,cci.qty,wb.workflowname,wb.workflowname||':'||w.workflowrevision workflowinfo,w.workflowid,cci.containertype,res.resourcename,res.description resourcedesc,wm_concat(DISTINCT e.fullname) apsPlanEmpName,apd.qty apsqty,
DECODE(cci.containertype,'2','批量加工','3','批量子批次','7','单件单卡','') containertypename,apd.workflowstepid,apd.workflowstepname,s.specid,SUBSTR(wfs.workflowstepname,0,INSTR(wfs.workflowstepname,'-')-1)  specno,
to_char(apd.starttime,'yyyy-mm-dd hh24:mi:ss') starttime,to_char(apd.endtime,'yyyy-mm-dd hh24:mi:ss') endtime,
to_char(apd.ResStartTime,'yyyy-mm-dd hh24:mi:ss') ResStartTime,to_char(apd.ResEndTime,'yyyy-mm-dd hh24:mi:ss') ResEndTime,api.apstype,DECODE(api.apstype,'1','正排','2','倒排','3','瓶颈排','') apstypename,api.apsplaninfoid,apd.workcenterid,apd.teamid,apd.resourceid
,res.productline,c.customobjectname customobjectitemsname,tm.teamname,(select count(1) from writetoothersystem wt where wt.parentid=apd.apsplandetailid and wt.recordtype like 'Aps_To%') logcount
FROM containercurrentinfo cci 
LEFT JOIN apsplaninfo api ON api.containerid = cci.containerid
LEFT JOIN apsplandetail apd ON apd.apsplaninfoid=api.apsplaninfoid
left join workflowstep wfs on wfs.workflowstepid=apd.workflowstepid
LEFT JOIN specbase sb ON sb.specbaseid = wfs.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,wfs.specid)
LEFT JOIN resourcedef res ON res.resourceid = apd.resourceid
LEFT JOIN Workflow w ON w.workflowid = api.workflowid
LEFT JOIN workflowbase wb ON wb.workflowbaseid = w.workflowbaseid 
LEFT JOIN Userattribute u ON u.parentid = w.factoryid AND u.userattributename = '检验序自动报工' 
LEFT JOIN apsplandetailemp ade ON ade.apsplandetailid = apd.apsplandetailid
LEFT JOIN employee e ON e.employeeid = ade.employeeid
LEFT JOIN customobject c on c.customobjectid =res.productline 
LEFT JOIN productteaminfo pti ON pti.productname = cci.productname AND pti.isuse = 0
LEFT JOIN team tm ON tm.teamid = pti.teamid
WHERE cci.originalfactoryid = '{0}' AND cci.isinpool=1 AND cci.containerstatus=1 AND cci.isinstore!=1  
AND api.apstype2=2 AND api.isuse = 0 AND apd.isuse = 0 
AND NOT EXISTS (SELECT 1 FROM containerstepworkreport csr WHERE csr.containerid = cci.containerid and csr.workflowstepid = apd.workflowstepid and csr.isuse = 0)  
AND NOT EXISTS (SELECT 1 FROM containerstepnoship css WHERE css.containerid = cci.containerid AND css.workflowstepid = apd.workflowstepid AND css.isuse= 0 AND css.noship = 2)
AND apd.SpecFactoryID = '{0}' AND NVL(NVL(wfs.controlcode,s.controlcode),'0') NOT IN ('LY05','LY07') </string>.Value

        strSql = String.Format(strSql, para("factory").ToString) '添加分厂筛选

        If Not String.IsNullOrWhiteSpace(para("mfgOrderName").ToString) Then
            strSql += String.Format(" AND cci.mfgordername LIKE '%{0}%'", para("mfgOrderName").ToString)
        End If

        If Not String.IsNullOrWhiteSpace(para("containerName").ToString) Then
            strSql += String.Format(" AND cci.containername LIKE '%{0}%'", para("containerName").ToString)
        End If

        If Not String.IsNullOrWhiteSpace(para("productName").ToString) Then
            strSql += String.Format(" AND cci.productname LIKE '%{0}%'", para("productName").ToString)
        End If

        If Not String.IsNullOrWhiteSpace(para("specNo").ToString) Then
            strSql += String.Format(" AND wfs.workflowstepname LIKE '%{0}%'", para("specNo").ToString)
        End If

        If Not String.IsNullOrWhiteSpace(para("startTime1").ToString) Then
            strSql += String.Format(" AND apd.starttime >= to_date('{0}','yyyy-MM-dd hh24:mi:ss')", para("startTime1").ToString)
        End If

        If Not String.IsNullOrWhiteSpace(para("startTime2").ToString) Then
            strSql += String.Format(" AND apd.starttime <= to_date('{0}','yyyy-MM-dd hh24:mi:ss')", para("startTime2").ToString)
        End If

        If Not String.IsNullOrWhiteSpace(para("endTime1").ToString) Then
            strSql += String.Format(" AND apd.endtime >= to_date('{0}','yyyy-MM-dd hh24:mi:ss')", para("endTime1").ToString)
        End If

        If Not String.IsNullOrWhiteSpace(para("endTime2").ToString) Then
            strSql += String.Format(" AND apd.endtime <= to_date('{0}','yyyy-MM-dd hh24:mi:ss')", para("endTime2").ToString)
        End If

        If Not String.IsNullOrWhiteSpace(para("apsType").ToString) Then
            strSql += String.Format(" AND api.apstype = '{0}'", para("apsType").ToString)
        End If

        If para("status").ToString = "0" Or String.IsNullOrWhiteSpace(para("status").ToString) Then '未下发
            If para.ContainsKey("CurWorkflow") Then '只看当前工艺的数据
                strSql += " and cci.workflowid=api.workflowid "
            End If
            strSql += " AND NOT EXISTS (SELECT 1 FROM apsplandetail APD2 WHERE APD2.CONTAINERID=CCI.CONTAINERID AND APD2.WORKFLOWSTEPID=APD.WORKFLOWSTEPID AND APD2.ISUSE=0 AND APD2.STATUS=1) "

            strSql += " AND nvl(apd.status,0) = 0 AND (u.attributevalue = '否' OR NVL(s.lastcheck,0) != 1)"
        Else
            strSql += String.Format(" AND apd.status = '{0}'", para("status").ToString)
        End If

        If Not String.IsNullOrWhiteSpace(para("workCenter").ToString) Then
            strSql += String.Format(" AND apd.workcenterid = '{0}'", para("workCenter").ToString)
        End If

        If Not String.IsNullOrWhiteSpace(para("team").ToString) Then
            strSql += String.Format(" AND apd.teamid = '{0}'", para("team").ToString)
        End If

        If para.ContainsKey("isProductLine") And Not String.IsNullOrWhiteSpace(para("isProductLine").ToString) Then
            strSql += String.Format(" AND res.productline is {0} null", IIf(para("isProductLine").ToString.Equals("1"), "Not", ""))
        End If

        '件号主制单元
        If para.ContainsKey("productteamid") And Not String.IsNullOrWhiteSpace(para("productteamid").ToString) Then
            strSql += String.Format(" AND pti.teamid = '{0}'", para("productteamid").ToString)
        End If

        '设备编码
        If para.ContainsKey("resourceCode") And Not String.IsNullOrWhiteSpace(para("resourceCode").ToString) Then
            strSql += String.Format(" AND res.resourcename like '%{0}%'", para("resourceCode").ToString)
        End If

        strSql += <string>  
 GROUP BY apd.apsplandetailid, cci.mfgorderid,cci.mfgordername,cci.containerid,cci.containername,cci.productid, cci.productdescription,
cci.productname,cci.qty,wb.workflowname,wb.workflowname,w.workflowrevision,w.workflowid,cci.containertype,res.description,apd.qty,
apd.workflowstepid,apd.workflowstepname,wfs.workflowstepname,s.specid,res.resourcename,res.productline,c.customobjectname,
apd.starttime,apd.endtime,api.apstype,api.apsplaninfoid,apd.workcenterid,apd.teamid,apd.resendtime,apd.resstarttime,apd.resourceid,tm.teamname
order by cci.containerid,apd.workflowstepid</string>.Value
        If para("status").ToString.Equals("5") Or para("status").ToString.Equals("6") Then
            strSql = String.Format("select t.*,wt.PRODUCTNAME DOWNFULLNAME,wt.CREATEDATE DOWNTIME,wt.SENDXML REASON,wt.RESXML REMARK from ({0}) t left join WRITETOOTHERSYSTEM wt ON wt.PARENTID =t.APSPLANDETAILID AND wt.RECORDTYPE ='Aps_ToExamineSend' AND wt.ISSUCCESS =0", strSql)
        End If
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("PageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSql.ToString()
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function
    ''' <summary>
    ''' 获取预排的排产数据
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetTryApsPlanDetailData(ByVal para As Dictionary(Of String, String)) As uMESProcessResult

        Dim strSql = <string>
        SELECT apd.apsplandetailid, cci.mfgorderid,cci.mfgordername,cci.containerid,cci.containername,cci.productid, cci.productdescription description,
        cci.productname,cci.qty,wb.workflowname,wb.workflowname||':'||w.workflowrevision workflowinfo,w.workflowid,cci.containertype,res.description resourcedesc,wm_concat(DISTINCT e.fullname) apsPlanEmpName,apd.qty apsqty,
        DECODE(cci.containertype,'2','批量加工','3','批量子批次','7','单件单卡','') containertypename,apd.workflowstepid,apd.workflowstepname,s.specid,SUBSTR(wfs.workflowstepname,0,INSTR(wfs.workflowstepname,'-')-1)  specno,
        to_char(apd.starttime,'yyyy-mm-dd hh24:mi:ss') starttime,to_char(apd.endtime,'yyyy-mm-dd hh24:mi:ss') endtime,
       to_char(apd.ResStartTime,'yyyy-mm-dd hh24:mi:ss') ResStartTime,to_char(apd.ResEndTime,'yyyy-mm-dd hh24:mi:ss') ResEndTime,api.apstype,DECODE(api.apstype,'1','正排','2','倒排','3','瓶颈排产','') apstypename,api.apsplaninfoid,apd.workcenterid,apd.teamid,apd.resourceid
        FROM containercurrentinfo cci 
        LEFT JOIN apsplaninfopre api ON api.containerid = cci.containerid
        LEFT JOIN apsplandetailpre apd ON apd.apsplaninfoid=api.apsplaninfoid
        left join workflowstep wfs on wfs.workflowstepid=apd.workflowstepid
        LEFT JOIN specbase sb ON sb.specbaseid = wfs.specbaseid
        LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,wfs.specid)
        LEFT JOIN resourcedef res ON res.resourceid = apd.resourceid
        LEFT JOIN Workflow w ON w.workflowid = api.workflowid
        LEFT JOIN workflowbase wb ON wb.workflowbaseid = w.workflowbaseid 
        LEFT JOIN containerstepworkreport csr ON csr.containerid = cci.containerid and csr.workflowstepid = apd.workflowstepid and csr.isuse = 0
        LEFT JOIN apsplandetailemp ade ON ade.apsplandetailid = apd.apsplandetailid
         LEFT JOIN employee e ON e.employeeid = ade.employeeid
        WHERE cci.originalfactoryid = '{0}' AND cci.isinpool=1 AND cci.containerstatus=1 AND cci.isinstore!=1  
        AND api.apstype2=2 AND api.isuse = 0 AND apd.isuse = 0 AND csr.containerstepworkreportid IS NULL
        AND apd.SpecFactoryID = '{0}' AND NVL(wfs.controlcode,s.controlcode) != 'LY05'
                     </string>.Value

        strSql = String.Format(strSql, para("factory").ToString) '添加分厂筛选

        If para.ContainsKey("CurWorkflow") Then '只看当前工艺的数据
            strSql += " and cci.workflowid=api.workflowid "
        End If

        If para.ContainsKey("mfgOrderName") Then
            If Not String.IsNullOrWhiteSpace(para("mfgOrderName").ToString) Then
                strSql += String.Format(" AND cci.mfgordername LIKE '%{0}%'", para("mfgOrderName").ToString)
            End If
        End If
        If para.ContainsKey("containerName") Then
            If Not String.IsNullOrWhiteSpace(para("containerName").ToString) Then
                strSql += String.Format(" AND cci.containername LIKE '%{0}%'", para("containerName").ToString)
            End If
        End If
        If para.ContainsKey("productName") Then
            If Not String.IsNullOrWhiteSpace(para("productName").ToString) Then
                strSql += String.Format(" AND cci.productname LIKE '%{0}%'", para("productName").ToString)
            End If
        End If
        If para.ContainsKey("specNo") Then
            If Not String.IsNullOrWhiteSpace(para("specNo").ToString) Then
                strSql += String.Format(" AND wfs.workflowstepname LIKE '%{0}%'", para("specNo").ToString)
            End If
        End If
        If para.ContainsKey("startTime") Then
            If Not String.IsNullOrWhiteSpace(para("startTime").ToString) Then
                strSql += String.Format(" AND apd.starttime >= to_date('{0}','yyyy-MM-dd')", para("startTime").ToString)
            End If
        End If
        If para.ContainsKey("endTime") Then
            If Not String.IsNullOrWhiteSpace(para("endTime").ToString) Then
                strSql += String.Format(" AND apd.endtime <= to_date('{0}','yyyy-MM-dd')", para("endTime").ToString)
            End If
        End If
        If para.ContainsKey("apsType") Then
            If Not String.IsNullOrWhiteSpace(para("apsType").ToString) Then
                strSql += String.Format(" AND api.apstype = '{0}'", para("apsType").ToString)
            End If
        End If
        If para.ContainsKey("status") Then
            If para("status").ToString = "0" Or String.IsNullOrWhiteSpace(para("status").ToString) Then
                strSql += String.Format(" AND nvl(apd.status,0) in (0,2)")
            Else
                strSql += String.Format(" AND apd.status = '{0}'", para("status").ToString)
            End If
        End If
        If para.ContainsKey("workCenter") Then
            If Not String.IsNullOrWhiteSpace(para("workCenter").ToString) Then
                strSql += String.Format(" AND apd.workcenterid = '{0}'", para("workCenter").ToString)
            End If
        End If
        If para.ContainsKey("team") Then
            If Not String.IsNullOrWhiteSpace(para("team").ToString) Then
                strSql += String.Format(" AND apd.teamid = '{0}'", para("team").ToString)
            End If
        End If
        strSql += <string>  
 GROUP BY apd.apsplandetailid, cci.mfgorderid,cci.mfgordername,cci.containerid,cci.containername,cci.productid, cci.productdescription,
cci.productname,cci.qty,wb.workflowname,wb.workflowname,w.workflowrevision,w.workflowid,cci.containertype,res.description,apd.qty,
apd.workflowstepid,apd.workflowstepname,wfs.workflowstepname,s.specid,
apd.starttime,apd.endtime,api.apstype,api.apsplaninfoid,apd.workcenterid,apd.teamid,apd.resendtime,apd.resstarttime,apd.resourceid
order by cci.containerid,apd.workflowstepid</string>.Value
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("PageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSql.ToString()
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 下发到手动排产
    ''' </summary>
    ''' <param name="para"></param>
    ''' <param name="dt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SendHandApsDatas(ByVal para As Dictionary(Of String, String), ByVal dt As DataTable, ByVal user As CurrentUserInfo) As uMESProcessResult
        Dim result = New uMESProcessResult()
        Try
            Dim strsql As New StringBuilder
            Dim sqlList As New ArrayList
            Dim sqlList77 As New ArrayList
            '将下发的记录手动点击按钮-发送到手动排产containerstepplan，开始结束时间取所选工序所有记录的最小开始和最大结束时间，
            '转工工序排产时间取当前时间，containerstepplan同批次同工序名称的只保留一条记录
            '所选的记录中可能存在多条记录对应一个批次一个工序对应多个设备的情况(并行加工)且这些记录不一定都选到
            '不考虑并行加工的多条记录部分下发的情况(理论上应该一起下发，否则涉及到回收重排问题)
            '不考虑所选的记录对应的工序workflowstepid不是当前批次工艺上workflowstepid的情况(批次调度，多工艺排产记录并存)
            '同批次同工序名称的如果存在，执行删除后重新插入

            '查询热表批次对应主制分厂批次是否存在手动排产(只针对热表)
            Dim strRbSqlQuery As String = <string>
                                        SELECT Distinct csp.containerstepplanid FROM container c
                                        LEFT JOIN containersubcontainer csc ON csc.subcontainerid = c.containerid
                                        LEFT JOIN containerstepplan csp ON csp.containerid = csc.containerid AND csp.plantype = '2'
                                        AND (csp.workflowstepid = csc.workflowstepid OR csp.workflowstepname = csc.workflowstepname)
                                        WHERE c.originalfactoryid = '{0}' AND c.containerid = '{1}'
                                        </string>.Value

            Dim apsType As String = dt.Rows(0)("apstype")
            '查询是否有手动排产记录
            Dim dtPlan As New DataTable
            Dim strsqlPlan As String = "select containerid from containerstepplan where containerid='{0}' and (workflowstepname='{1}' OR SUBSTR(workflowstepname,0,INSTR(workflowstepname,'-')-1) = '{2}') "

            Dim strsqlAps As String =
                <string>
                        select apt.containerid,apt.workflowstepid,apt.workflowstepname,to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') createdate,
                        apt.specfactoryid,cc.originalfactoryid,ap.createemployeeid,cc.originalfactoryid,
                        min(to_char(apt.starttime,'yyyy-mm-dd hh24:mi:ss')) PlannedStartDate,max(to_char(apt.endtime,'yyyy-mm-dd hh24:mi:ss')) PlannedFinishDate
                        from apsplaninfo ap
                        left join apsplandetail apt on ap.apsplaninfoid=apt.apsplaninfoid
                        left join containercurrentinfo cc on cc.containerid=ap.containerid 
                        where apt.containerid='{0}' and apt.workflowstepid='{1}' and apt.isuse=0 and ap.apstype2=2 and ap.apstype={2}
                        group by  apt.containerid,apt.workflowstepid,apt.workflowstepname,
                        apt.specfactoryid,cc.originalfactoryid,ap.createemployeeid,apt.SpecFactoryid                       
                    </string>
            Dim dtFilter As DataTable = dt.DefaultView.ToTable(True, "containerid", "workflowstepid", "workflowstepname", "specno")

            Dim specPlanSql As String =
                <string>
                            insert into containerstepplan csp (csp.containerid,csp.containerstepplanid,csp.plannedstartdate,csp.plannedfinishdate,csp.workflowstepid,workflowstepname,
                              csp.plantype,csp.stepplancreatedate,csp.stepplancreateemployeeid,specfactoryid,csp.stepplanshipdate,csp.stepplanshipemployeeid)
                              values ('{0}',sys_guid(),to_date('{1}','yyyy/mm/dd hh24:mi:ss'),to_date('{2}','yyyy/mm/dd hh24:mi:ss'),'{3}','{4}','{5}',to_date('{6}','yyyy/mm/dd hh24:mi:ss'),'{7}','{8}',to_date('{9}','yyyy/mm/dd hh24:mi:ss'),'{10}') 
                        </string>

            Dim stepPlanBoard As String = "UPDATE r_containerstepplanboard t SET t.plannedfinishdate = to_date('{0}','yyyy/mm/dd hh24:mi:ss'),t.stepplancreatedate = to_date('{1}','yyyy/mm/dd hh24:mi:ss') WHERE t.containerid = '{2}' AND t.workflowstepid = '{3}'" '更新看板数据
            For Each drF In dtFilter.Rows
                strsql.Clear()
                Dim dtRbPlan = DatabaseHelper.Query_ReturnDatTable(String.Format(strRbSqlQuery, rbFactoryID, drF("containerid").ToString()))
                '如果是热表排产，则验证对应的主制批次工序是否手动排产
                If dtRbPlan.Rows.Count > 0 Then
                    If dtRbPlan.Select("containerstepplanid is null").Length > 0 Then
                        result.Message = "主制批次工序未发布到看板"
                        result.Result = False
                        Return result
                        Exit Function
                    End If
                End If
                dtPlan = DatabaseHelper.Query_ReturnDatTable(String.Format(strsqlPlan, drF("containerid").ToString, drF("workflowstepname").ToString, drF("specno").ToString))
                If dtPlan.Rows.Count > 0 Then
                    strsql.AppendFormat("delete from containerstepplan where containerid='{0}' and (workflowstepname='{1}' OR SUBSTR(workflowstepname,0,INSTR(workflowstepname,'-')-1) = '{2}')", drF("containerid").ToString, drF("workflowstepname").ToString, drF("specno").ToString)
                    sqlList.Add(strsql.ToString)
                End If

                '理论上只有一条
                dtPlan = DatabaseHelper.Query_ReturnDatTable(String.Format(strsqlAps, drF("containerid").ToString, drF("workflowstepid").ToString, apsType))
                Dim plantype As String = ""
                For Each dr As DataRow In dtPlan.Rows
                    '更改热表批次对应主制批次工序的手动排产数据
                    For Each drR As DataRow In dtRbPlan.Rows
                        sqlList.Add(String.Format("UPDATE containerstepplan csp SET csp.plannedfinishdate = to_date('{0}','yyyy/mm/dd hh24:mi:ss'),csp.isshiplocked = 1,csp.stepplanshipdate = SYSDATE,csp.stepplanshipemployeeid = '{1}' WHERE csp.containerstepplanid = '{2}'", dr("PlannedFinishDate").ToString(), para("EmployeeID"), drR("containerstepplanid").ToString()))
                    Next
                    '插入本批次的手动排产
                    Dim stepplanshipemployeeid As String = ""
                    Dim createdate As String = dr("createdate").ToString() '为保证两个栏位有相同的时间
                    Dim stepplanshipdate As String = ""
                    If dr("SpecFactoryid").ToString = dr("originalfactoryid").ToString Then
                        plantype = "1"
                        stepplanshipdate = ""
                        stepplanshipemployeeid = ""
                    Else
                        plantype = "2"
                        stepplanshipemployeeid = dr("createemployeeid").ToString()
                        stepplanshipdate = createdate
                    End If

                    sqlList.Add(String.Format(specPlanSql,
                                dr("containerid").ToString, dr("plannedstartdate").ToString(), dr("plannedfinishdate").ToString(),
                                dr("workflowstepid").ToString, dr("workflowstepname").ToString, plantype, DateTime.Now.ToString(), user.EmployeeID,
                                dr("SpecFactoryid").ToString, stepplanshipdate, stepplanshipemployeeid)
                                )
                    sqlList77.Add(String.Format(stepPlanBoard, dr("plannedfinishdate").ToString(), DateTime.Now.ToString(), dr("containerid").ToString, dr("workflowstepid").ToString))
                    Exit For
                Next
            Next

            DatabaseHelper.ExecuteSqlTran(sqlList)
            DatabaseHelper.ExecuteSqlTranBy77(sqlList77)
            result.Message = "下发到手动排产成功"
            result.Result = True
        Catch ex As Exception
            result.Message = ex.Message
            result.Result = False
        End Try
        Return result
    End Function

    ''' <summary>
    ''' 下发到手动排产（针对转工序排产转出） 
    ''' </summary>
    ''' <param name="apsPlanDetailIDs"></param>
    ''' <param name="user"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SendHandApsDatasByShipOut(ByVal apsPlanDetailIDs As String, ByVal user As CurrentUserInfo) As ArrayList
        Dim strsql As New StringBuilder
        Dim sqlList As New ArrayList
        Dim strsqlAps As String =
            <string>
                        SELECT distinct apt.containerid,apt.workflowstepid,apt.workflowstepname,apt.shipreceiveempid,to_char(apt.shipreceivedate,'yyyy-mm-dd hh24:mi:ss') shipreceivedate,
                        apt.specfactoryid,ap.createemployeeid,s.specno,to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') createdate,
                        decode(apt.shiprecplanstartdate,NULL,to_char(apt.starttime,'yyyy-mm-dd hh24:mi:ss'),to_char(apt.shiprecplanstartdate,'yyyy-mm-dd hh24:mi:ss')) PlannedStartDate,
                        DECODE(apt.shiprecplanenddate,NULL, to_char(apt.endtime,'yyyy-mm-dd hh24:mi:ss'),to_char(apt.shiprecplanenddate,'yyyy-mm-dd hh24:mi:ss')) PlannedFinishDate
                        FROM apsplandetail apt
                        LEFT JOIN apsplaninfo ap ON ap.apsplaninfoid = apt.apsplaninfoid
                        LEFT JOIN Workflowstep wfs ON wfs.workflowstepid = apt.workflowstepid
                        LEFT JOIN specbase sb ON sb.specbaseid = wfs.specbaseid
                        LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,wfs.specid)
                        WHERE apt.apsplandetailid IN ({0}) AND ap.isuse = 0 AND apt.isuse = 0
            </string>
        '插入Sql
        Dim specPlanSql As String =
            <string>
                     insert into containerstepplan csp (csp.containerid,csp.containerstepplanid,csp.plannedstartdate,csp.plannedfinishdate,csp.workflowstepid,workflowstepname,
                     csp.plantype,csp.stepplancreatedate,csp.stepplancreateemployeeid,specfactoryid,csp.stepplanshipdate,csp.stepplanshipemployeeid,isshiplocked)
                     values ('{0}',sys_guid(),to_date('{1}','yyyy/mm/dd hh24:mi:ss'),to_date('{2}','yyyy/mm/dd hh24:mi:ss'),'{3}','{4}','{5}',to_date('{6}','yyyy/mm/dd hh24:mi:ss'),'{7}','{8}',to_date('{9}','yyyy/mm/dd hh24:mi:ss'),'{10}',{11}) 
            </string>.Value

        Dim dtRes = DatabaseHelper.Query_ReturnDatTable(String.Format(strsqlAps, apsPlanDetailIDs))
        For Each dr In dtRes.Rows
            strsql.Clear()
            strsql.AppendFormat("delete from containerstepplan where containerid='{0}' and (workflowstepname='{1}' OR SUBSTR(workflowstepname,0,INSTR(workflowstepname,'-')-1) = '{2}')", dr("containerid").ToString, dr("workflowstepname").ToString, dr("specno").ToString)
            If Not sqlList.Contains(strsql.ToString()) Then
                sqlList.Add(strsql.ToString())
            End If
            '插入本批次的手动排产
            strsql.Clear()
            strsql.AppendFormat(specPlanSql,
                        dr("containerid").ToString, dr("PlannedStartDate").ToString(), dr("PlannedFinishDate").ToString(),
                        dr("workflowstepid").ToString, dr("workflowstepname").ToString, "2", DateTime.Now.ToString(), user.EmployeeID,
                        dr("SpecFactoryid").ToString, dr("shipreceivedate").ToString(), dr("shipreceiveempid").ToString(), 1)
            If Not sqlList.Contains(strsql.ToString()) Then
                sqlList.Add(strsql.ToString())
            End If
        Next
        DatabaseHelper.ExecuteSqlTran(sqlList)
        Return sqlList
    End Function

    ''' <summary>
    ''' 下发到手动排产（针对转工序排产接收） 
    ''' </summary>
    ''' <param name="apsPlanDetailIDs"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SendHandApsDatasByShipReceive(ByVal apsPlanDetailIDs As String) As uMESProcessResult
        Dim result = New uMESProcessResult()
        Try
            Dim strsql As New StringBuilder
            Dim sqlList As New ArrayList
            Dim sqlList77 As New ArrayList
            Dim strsqlAps As String =
                <string>
                        SELECT distinct apt.containerid,apt.workflowstepid,apt.workflowstepname,apt.shipreceiveempid,to_char(apt.shipreceivedate,'yyyy-mm-dd hh24:mi:ss') shipreceivedate,
                        apt.specfactoryid,ap.createemployeeid,s.specno,to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') createdate,
                        decode(apt.shiprecplanstartdate,NULL,to_char(apt.starttime,'yyyy-mm-dd hh24:mi:ss'),to_char(apt.shiprecplanstartdate,'yyyy-mm-dd hh24:mi:ss')) PlannedStartDate,
                        DECODE(apt.shiprecplanenddate,NULL, to_char(apt.endtime,'yyyy-mm-dd hh24:mi:ss'),to_char(apt.shiprecplanenddate,'yyyy-mm-dd hh24:mi:ss')) PlannedFinishDate
                        FROM apsplandetail apt
                        LEFT JOIN apsplaninfo ap ON ap.apsplaninfoid = apt.apsplaninfoid
                        LEFT JOIN Workflowstep wfs ON wfs.workflowstepid = apt.workflowstepid
                        LEFT JOIN specbase sb ON sb.specbaseid = wfs.specbaseid
                        LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,wfs.specid)
                        WHERE apt.apsplandetailid IN ({0}) AND ap.isuse = 0 AND apt.isuse = 0
            </string>
            '插入Sql
            Dim specPlanSql As String =
                <string>
                     insert into containerstepplan csp (csp.containerid,csp.containerstepplanid,csp.plannedstartdate,csp.plannedfinishdate,csp.workflowstepid,workflowstepname,
                     csp.plantype,csp.stepplancreatedate,csp.stepplancreateemployeeid,specfactoryid,csp.stepplanshipdate,csp.stepplanshipemployeeid,isshiplocked)
                     values ('{0}',sys_guid(),to_date('{1}','yyyy/mm/dd hh24:mi:ss'),to_date('{2}','yyyy/mm/dd hh24:mi:ss'),'{3}','{4}','{5}',to_date('{6}','yyyy/mm/dd hh24:mi:ss'),'{7}','{8}',to_date('{9}','yyyy/mm/dd hh24:mi:ss'),'{10}',{11}) 
            </string>.Value
            Dim stepPlanBoard As String = "UPDATE r_containerstepplanboard t SET t.plannedfinishdate = to_date('{0}','yyyy/mm/dd hh24:mi:ss'),t.stepplancreatedate = to_date('{1}','yyyy/mm/dd hh24:mi:ss') WHERE t.containerid = '{2}' AND t.workflowstepid = '{3}'" '更新看板数据
            Dim dtRes = DatabaseHelper.Query_ReturnDatTable(String.Format(strsqlAps, apsPlanDetailIDs))
            For Each dr In dtRes.Rows
                '查询是否存在手动排产，有则更新
                strsqlAps = String.Format("select cp.stepplancreateemployeeid,to_char(cp.stepplancreatedate,'yyyy/mm/dd hh24:mi:ss') stepplancreatedate from containerstepplan cp where cp.containerid='{0}' and (cp.workflowstepname='{1}' OR SUBSTR(cp.workflowstepname,0,INSTR(cp.workflowstepname,'-')-1) = '{2}')", dr("containerid").ToString, dr("workflowstepname").ToString, dr("specno").ToString)
                Dim dtPlan = DatabaseHelper.Query_ReturnDatTable(strsqlAps)
                If dtPlan.Rows.Count = 0 Then
                    Continue For
                End If
                strsql.Clear()
                strsql.AppendFormat("delete from containerstepplan where containerid='{0}' and (workflowstepname='{1}' OR SUBSTR(workflowstepname,0,INSTR(workflowstepname,'-')-1) = '{2}')", dr("containerid").ToString, dr("workflowstepname").ToString, dr("specno").ToString)
                If Not sqlList.Contains(strsql.ToString()) Then
                    sqlList.Add(strsql.ToString())
                End If
                '插入本批次的手动排产
                strsql.Clear()
                strsql.AppendFormat(specPlanSql,
                            dr("containerid").ToString, dr("PlannedStartDate").ToString(), dr("PlannedFinishDate").ToString(),
                            dr("workflowstepid").ToString, dr("workflowstepname").ToString, "2", dtPlan.Rows(0)("stepplancreatedate").ToString(), dtPlan.Rows(0)("stepplancreateemployeeid").ToString(),
                            dr("SpecFactoryid").ToString, dr("shipreceivedate").ToString(), dr("shipreceiveempid").ToString(), 1)
                If Not sqlList.Contains(strsql.ToString()) Then
                    sqlList.Add(strsql.ToString())
                End If
                sqlList77.Add(String.Format(stepPlanBoard, dr("plannedfinishdate").ToString(), DateTime.Now.ToString(), dr("containerid").ToString, dr("workflowstepid").ToString))
            Next
            DatabaseHelper.ExecuteSqlTran(sqlList)
            DatabaseHelper.ExecuteSqlTranBy77(sqlList77)
            result.Result = True
        Catch ex As Exception
            result.Result = False
            result.Message = "发送看板失败，原因：" + ex.Message
        End Try
        Return result
    End Function

    ''' <summary>
    ''' 将预排数据发送到手动排产
    ''' </summary>
    ''' <param name="para"></param>
    ''' <param name="dt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SendHandApsDatas2(ByVal para As Dictionary(Of String, String), ByVal dt As DataTable) As uMESProcessResult
        Dim result = New uMESProcessResult()
        Try
            Dim strsql As New StringBuilder
            Dim sqlList As New ArrayList
            '将下发的记录手动点击按钮-发送到手动排产containerstepplan，开始结束时间取所选工序所有记录的最小开始和最大结束时间，
            '转工工序排产时间取当前时间，containerstepplan同批次同工序名称的只保留一条记录
            '所选的记录中可能存在多条记录对应一个批次一个工序对应多个设备的情况(并行加工)且这些记录不一定都选到
            '不考虑并行加工的多条记录部分下发的情况(理论上应该一起下发，否则涉及到回收重排问题)
            '不考虑所选的记录对应的工序workflowstepid不是当前批次工艺上workflowstepid的情况(批次调度，多工艺排产记录并存)
            '同批次同工序名称的如果存在，执行删除后重新插入

            Dim apsType As String = dt.Rows(0)("apstype")
            '查询是否有手动排产记录
            Dim dtPlan As New DataTable
            Dim strsqlPlan As String = "select containerid from containerstepplan where containerid='{0}' and workflowstepname='{1}' "

            Dim strsqlAps As String =
                <string>
                        select apt.containerid,apt.workflowstepid,apt.workflowstepname,to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') createdate,
                        apt.specfactoryid,cc.originalfactoryid,ap.createemployeeid,cc.originalfactoryid,
                        min(to_char(apt.starttime,'yyyy-mm-dd hh24:mi:ss')) PlannedStartDate,max(to_char(apt.endtime,'yyyy-mm-dd hh24:mi:ss')) PlannedFinishDate
                        from apsplaninfopre ap
                        left join apsplandetailpre apt on ap.apsplaninfoid=apt.apsplaninfoid
                        left join containercurrentinfo cc on cc.containerid=ap.containerid 
                        where apt.containerid='{0}' and apt.workflowstepid='{1}' and apt.isuse=0 and ap.apstype2=2 and ap.apstype={2}
                        group by  apt.containerid,apt.workflowstepid,apt.workflowstepname,
                        apt.specfactoryid,cc.originalfactoryid,ap.createemployeeid,apt.SpecFactoryid                       
                    </string>
            Dim dtFilter As DataTable = dt.DefaultView.ToTable(True, "containerid", "workflowstepid", "workflowstepname")

            Dim specPlanSql As String =
                <string>
                            insert into containerstepplan csp (csp.containerid,csp.containerstepplanid,csp.plannedstartdate,csp.plannedfinishdate,csp.workflowstepid,workflowstepname,
                              csp.plantype,csp.stepplancreatedate,csp.stepplancreateemployeeid,specfactoryid,csp.stepplanshipdate,csp.stepplanshipemployeeid)
                              values ('{0}',sys_guid(),to_date('{1}','yyyy/mm/dd hh24:mi:ss'),to_date('{2}','yyyy/mm/dd hh24:mi:ss'),'{3}','{4}','{5}',to_date('{6}','yyyy/mm/dd hh24:mi:ss'),'{7}','{8}',to_date('{9}','yyyy/mm/dd hh24:mi:ss'),'{10}') 
                        </string>
            For Each drF In dtFilter.Rows
                strsql.Clear()
                dtPlan = DatabaseHelper.Query_ReturnDatTable(String.Format(strsqlPlan, drF("containerid").ToString, drF("workflowstepname").ToString))
                If dtPlan.Rows.Count > 0 Then
                    strsql.AppendFormat("delete from containerstepplan where containerid='{0}' and workflowstepname='{1}'", drF("containerid").ToString, drF("workflowstepname").ToString)
                    sqlList.Add(strsql.ToString)
                End If

                '理论上只有一条
                dtPlan = DatabaseHelper.Query_ReturnDatTable(String.Format(strsqlAps, drF("containerid").ToString, drF("workflowstepid").ToString, apsType))
                Dim plantype As String = ""
                For Each dr As DataRow In dtPlan.Rows
                    Dim stepplanshipemployeeid As String = ""
                    Dim createdate As String = dr("createdate").ToString() '为保证两个栏位有相同的时间
                    Dim stepplanshipdate As String = ""
                    If dr("SpecFactoryid").ToString = dr("originalfactoryid").ToString Then
                        plantype = "1"
                        stepplanshipdate = ""
                        stepplanshipemployeeid = ""
                    Else
                        plantype = "2"
                        stepplanshipemployeeid = dr("createemployeeid").ToString()
                        stepplanshipdate = createdate
                    End If

                    sqlList.Add(String.Format(specPlanSql,
                                dr("containerid").ToString, dr("plannedstartdate").ToString(), dr("plannedfinishdate").ToString(),
                                dr("workflowstepid").ToString, dr("workflowstepname").ToString, plantype, createdate, dr("createemployeeid").ToString(),
                                dr("SpecFactoryid").ToString, stepplanshipdate, stepplanshipemployeeid)
                                )
                    Exit For
                Next
            Next

            DatabaseHelper.ExecuteSqlTran(sqlList)
            result.Message = "下发到手动排产成功"
            result.Result = True
        Catch ex As Exception
            result.Message = ex.Message
            result.Result = False
        End Try
        Return result
    End Function

    ''' <summary>
    ''' 保存排产下发、回收、删除
    ''' </summary>
    ''' <param name="para"></param>
    ''' <param name="dt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveApsPlanDatas(ByVal para As Dictionary(Of String, String), ByVal dt As DataTable) As uMESProcessResult
        Dim result = New uMESProcessResult()
        Try
            Dim strsql As New StringBuilder
            Dim sqlList As New ArrayList
            Dim dtTemp As DataTable = dt.Clone
            If para("type") = "Send" Then '下发
                result = SendApsPlanDatas(para, dt)
            ElseIf para("type") = "ReCall" Then '回收 （工序添加不加工时，也会调用回收方法）
                result = VaildRecall(dt) '验证回收
                If Not result.Result Then
                    Return result
                    Exit Function
                End If

                If dt.Columns.Contains("apsPlanDetailId") Then '如果是排产回收
                    For Each dr As DataRow In dt.Rows
                        strsql.Clear()
                        strsql.AppendLine("UPDATE apsplandetail ap SET ")
                        strsql.AppendFormat("ap.recycleemp = '{0}',", para("EmployeeID"))
                        strsql.AppendFormat("ap.recycledate = {0},ap.lastchangedate = sysdate,ap.status = '0'", "sysdate")
                        strsql.AppendFormat(" WHERE ap.apsplandetailid = '{0}'", dr("apsPlanDetailId"))
                        sqlList.Add(strsql.ToString)
                        ''添加回收操作到记录表中'
                        strsql.Clear()
                        strsql.AppendLine("INSERT INTO apsplanupdaterecord(apsplanupdaterecordid,containerid,specid,workflowstepid,updateempid,updatedate,status) VALUES(")
                        strsql.AppendFormat("'{0}',", Guid.NewGuid().ToString())
                        strsql.AppendFormat("'{0}',", dr("containerid").ToString())
                        strsql.AppendFormat("'{0}',", dr("specid").ToString())
                        strsql.AppendFormat("'{0}',", dr("workflowstepid").ToString())
                        strsql.AppendFormat("'{0}',", para("EmployeeID"))
                        strsql.AppendLine("sysdate,2)")
                        sqlList.Add(strsql.ToString)
                    Next
                Else '如果不存在排产明细，则直接回收该工序的所有排产明细
                    For Each dr As DataRow In dt.Rows
                        strsql.Clear()
                        strsql.AppendLine("UPDATE apsplandetail ap SET ")
                        strsql.AppendFormat("ap.recycleemp = '{0}',", para("EmployeeID"))
                        strsql.AppendFormat("ap.recycledate = {0},ap.lastchangedate = sysdate,ap.status = '0'", "sysdate")
                        strsql.AppendLine(" WHERE ap.apsplandetailid IN (SELECT ap1.apsplandetailid FROM apsplandetail ap1")
                        strsql.AppendLine(" LEFT JOIN apsplaninfo ai ON ai.apsplaninfoid = ap1.apsplaninfoid")
                        strsql.AppendFormat(" WHERE ai.containerid = '{0}' AND ap1.workflowstepid = '{1}' AND ai.isuse = 0 AND ap1.isuse = 0)", dr("ContainerId").ToString(), dr("WorkflowStepId").ToString())
                        sqlList.Add(strsql.ToString)
                    Next
                End If

                '删除派工记录
                Dim failflag As Boolean = False
                For Each dr As DataRow In dt.Rows
                    dtTemp.Rows.Clear()
                    dtTemp.Rows.Add(dr.ItemArray)
                    If Not ApsRecallDispatchStepinfo(dt) Then
                        failflag = True
                    End If
                    If Not ApsRecallDispatch(dt) Then
                        failflag = True
                    End If
                    'Dim sql As String = String.Format("select si.workflowstepid from stepinfo si where si.workflowstepid='{0}'  ", dr("workflowstepid"))
                    'If DatabaseHelper.Query_ReturnDatTable(sql).Rows.Count > 0 Then
                    '    If Not ApsRecallDispatchStepinfo(dt) Then
                    '        failflag = True
                    '    End If
                    'Else
                    '    If Not ApsRecallDispatch(dt) Then
                    '        failflag = True
                    '    End If
                    'End If
                Next
                'If Not ApsRecallDispatch(dt) Then
                If failflag Then
                    result.Message = "删除派工出错"
                    result.Result = False
                    Return result
                    Exit Function
                End If
                DatabaseHelper.ExecuteSqlTran(sqlList)
                result.Message = "回收成功"
                result.Result = True
            ElseIf para("type") = "Dele" Then '删除
                result = VaildDele(dt) '验证删除
                If Not result.Result Then
                    Return result
                    Exit Function
                End If
                Dim sqlListBy77 As New ArrayList()
                Dim dtMain = dt.DefaultView.ToTable(True, "apsplaninfoid")
                For Each drm As DataRow In dtMain.Rows
                    Dim detailIds = ""
                    Dim drsDetail = dt.Select(String.Format("apsplaninfoid='{0}'", drm("apsplaninfoid").ToString()))
                    For Each item As DataRow In drsDetail
                        If String.IsNullOrWhiteSpace(item("apsPlanDetailId").ToString()) Then
                            Continue For
                        End If
                        'Modify by Yuxj20230323 update--->delete
                        strsql.Clear()
                        strsql.AppendFormat("DELETE containerstepplan cp WHERE cp.containerid = '{0}' AND cp.workflowstepid = '{1}'", item("containerid").ToString(), item("workflowstepid").ToString())
                        sqlList.Add(strsql.ToString)

                        '删除77数据库中的数据
                        sqlListBy77.Add(String.Format("DELETE r_containerstepplanboard t WHERE t.containerid = '{0}' AND t.workflowstepid = '{1}'", item("containerid").ToString(), item("workflowstepid").ToString()))

                        strsql.Clear()
                        'strsql.AppendFormat("UPDATE apsplandetail apd SET apd.isuse = 1 WHERE apd.apsplandetailid = '{0}'", item("apsPlanDetailId").ToString())
                        strsql.AppendFormat("delete from apsplandetail apd WHERE apd.apsplandetailid = '{0}'", item("apsPlanDetailId").ToString())
                        sqlList.Add(strsql.ToString)
                        strsql.Clear()
                        'strsql.AppendFormat("UPDATE apsplandetailemptime apt SET apt.isuse = 1 WHERE apt.apsplandetailempid IN (SELECT ape.apsplandetailempid FROM apsplandetailemp ape WHERE ape.apsplandetailid = '{0}')", item("apsPlanDetailId").ToString())
                        strsql.AppendFormat("delete from apsplandetailemptime apt WHERE apt.apsplandetailempid IN (SELECT ape.apsplandetailempid FROM apsplandetailemp ape WHERE ape.apsplandetailid = '{0}')", item("apsPlanDetailId").ToString())
                        sqlList.Add(strsql.ToString)
                        strsql.Clear()
                        'strsql.AppendFormat("UPDATE apsplandetailemp ape SET ape.isuse = 1 WHERE ape.apsplandetailid = '{0}'", item("apsPlanDetailId").ToString())
                        strsql.AppendFormat("delete from apsplandetailemp ape WHERE ape.apsplandetailid = '{0}'", item("apsPlanDetailId").ToString())
                        sqlList.Add(strsql.ToString)
                        detailIds += String.Format("'{0}',", item("apsPlanDetailId").ToString())
                    Next
                    detailIds = detailIds.Trim(",")
                    strsql.Clear()
                    strsql.AppendFormat("SELECT 1 FROM apsplandetail apd  WHERE apd.apsplaninfoid = '{0}' AND apd.isuse = 0 AND apd.apsplandetailid NOT IN ({1})", drm("apsplaninfoid").ToString(), detailIds)
                    '如果不存在明细，则删除主表
                    If DatabaseHelper.GetSingle(strsql.ToString()) Is Nothing Then
                        strsql.Clear()
                        'strsql.AppendFormat("UPDATE apsplaninfo api SET api.isuse = 1 WHERE api.apsplaninfoid = '{0}'", drm("apsplaninfoid").ToString())
                        strsql.AppendFormat("delete from apsplaninfo api WHERE api.apsplaninfoid = '{0}'", drm("apsplaninfoid").ToString())
                        sqlList.Add(strsql.ToString)
                    End If
                Next
                DatabaseHelper.ExecuteSqlTran(sqlList)
                DatabaseHelper.ExecuteSqlTranBy77(sqlListBy77)
                result.Message = "删除成功"
                result.Result = True
            Else
                result.Message = "无效操作"
                result.Result = False
            End If
        Catch ex As Exception
            result.Message = ex.Message
            result.Result = False
        End Try
        Return result
    End Function


    ''' <summary>
    ''' 保存排产下发
    ''' add:Wangjh 20240115
    ''' </summary>
    ''' <param name="para"></param>
    ''' <param name="dt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SendApsPlanDatas(ByVal para As Dictionary(Of String, String), ByVal dt As DataTable) As uMESProcessResult
        Dim result = New uMESProcessResult()
        Dim strsql As New StringBuilder
        Dim sqlList As New ArrayList
        Dim dtTemp As DataTable = dt.Clone

        result = IsSend(dt, para) '验证下发
        If Not result.Result Then
            Return result
            Exit Function
        End If
        For Each dr As DataRow In dt.Rows
            strsql.Clear()
            strsql.AppendLine("UPDATE apsplandetail ap SET ")
            strsql.AppendFormat("ap.outemp = '{0}',", para("EmployeeID"))
            strsql.AppendLine("ap.outdate = sysdate,ap.lastchangedate = sysdate,ap.status = '1'")
            strsql.AppendFormat(" WHERE ap.apsplandetailid = '{0}'", dr("apsPlanDetailId"))
            sqlList.Add(strsql.ToString)
        Next
        DatabaseHelper.ExecuteSqlTran(sqlList) '先执行下发，才能派工
        Try
            sqlList.Clear()
            'yuxj20220826
            '有工步的派到工步，没有工步的到工序 如结构件有些有工步有些没有工步的如何处理？
            For Each dr As DataRow In dt.Rows
                dtTemp.Rows.Clear()
                dtTemp.Rows.Add(dr.ItemArray)
                If para("FactoryName").Contains("热表") Then '修改为只有热表分厂下发到工步
                    sqlList.AddRange(ApsAutoDispatchStepInfo(dtTemp, para))
                Else
                    sqlList.AddRange(ApsAutoDispatch(dtTemp, para))
                End If
            Next
            '自动申请生产准备 add by zhangrj 2022-10-31
            sqlList.AddRange(ApsAutoProductPreparationInfo(dt, para))
            DatabaseHelper.ExecuteSqlTran(sqlList) '统一执行sql
            result.Message = "下发成功"
        Catch ex As Exception
            '自动派工报错后，执行自动回收
            sqlList.Clear()
            For Each dr As DataRow In dt.Rows
                strsql.Clear()
                strsql.AppendLine("UPDATE apsplandetail ap SET ")
                strsql.AppendFormat("ap.recycleemp = '{0}',", para("EmployeeID"))
                strsql.AppendLine("ap.recycledate = sysdate,ap.lastchangedate = sysdate,ap.status = '0'")
                strsql.AppendFormat(" WHERE ap.apsplandetailid = '{0}'", dr("apsPlanDetailId"))
                sqlList.Add(strsql.ToString)
            Next
            DatabaseHelper.ExecuteSqlTran(sqlList)
            result.Result = False
            result.Message = "下发失败，原因：" + ex.Message
        End Try

        Return result
    End Function

    ''' <summary>
    ''' 验证下发数据
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function IsSend(ByVal dt As DataTable, ByVal param As Dictionary(Of String, String)) As uMESProcessResult
        Dim result = New uMESProcessResult()
        result.Result = True
        Dim sql As String = ""
        For Each dr As DataRow In dt.Rows
            '验证工序是否存在下发记录
            sql = <string>
            SELECT 1 FROM apsplandetail ap
            LEFT JOIN apsplaninfo ai ON ai.apsplaninfoid = ap.apsplaninfoid
            WHERE ap.status = 1 AND ap.isuse = 0 AND ai.isuse = 0 AND ai.apstype2=2
            AND ai.containerid = '{0}' AND ap.workflowstepname = '{1}'</string>.Value
            sql = String.Format(sql, dr("containerId").ToString, dr("workflowstepname").ToString)
            'Dim dtq As DataTable = DatabaseHelper.Query_ReturnDatTable(sql.ToString())
            If Not DatabaseHelper.GetSingle(sql.ToString()) Is Nothing Then
                result.Result = False
                result.Message = "所选批次：" + dr("containerName").ToString + "的工序：" + dr("workflowstepName").ToString + " 存在下发记录，请检查当前工艺或其他版本的下发记录。"
                Exit For
            End If
            '验证工序是否存在不加工记录
            sql = <string>
            SELECT 1 FROM containerstepnoship css 
            WHERE css.containerid = '{0}' AND css.workflowstepid = '{1}' 
            AND css.isuse= 0 AND css.noship = 2</string>.Value
            sql = String.Format(sql, dr("containerId").ToString, dr("workflowstepid").ToString)
            If Not DatabaseHelper.GetSingle(sql.ToString()) Is Nothing Then
                result.Result = False
                result.Message = "所选批次：" + dr("containerName").ToString + "的工序：" + dr("workflowstepName").ToString + " 存在不加工记录，请检查当前工艺工序不加工情况"
                Exit For
            End If
            '验证多机并行是否全选
            sql = <string>
            SELECT aps.apsplandetailid FROM apsplandetail aps 
            LEFT JOIN apsplaninfo ai ON ai.apsplaninfoid = aps.apsplaninfoid
            WHERE ai.containerid = '{0}' AND aps.workflowstepid = '{1}'
            AND aps.isuse = 0 AND ai.isuse = 0 AND ai.apstype = '{2}' AND ai.apstype2 = 2</string>.Value
            sql = String.Format(sql, dr("containerId").ToString, dr("workflowstepid").ToString, dr("apstype").ToString)
            Dim dtSub = DatabaseHelper.Query_ReturnDatTable(sql)

            If dtSub.Rows.Count <> dt.Select(String.Format("containerId='{0}' and workflowstepid = '{1}' and apstype = '{2}'", dr("containerId").ToString, dr("workflowstepid").ToString, dr("apstype").ToString)).Length Then
                result.Result = False
                result.Message = "所选批次：" + dr("containerName").ToString + "的工序：" + dr("workflowstepName").ToString + " 存在未勾选的排产记录，请重新选择后下发"
                Exit For
            End If
            '验证是否合卡
            sql = String.Format("SELECT 1 FROM containercombinate cb WHERE cb.containerid = '{0}'", dr("containerId").ToString)
            If Not DatabaseHelper.GetSingle(sql) Is Nothing Then
                result.Result = False
                result.Message = "所选批次：" + dr("containerName").ToString + "已合卡，请删除合卡后再下发"
                Exit For
            End If
            '验证是否外委
            sql = String.Format("SELECT 1 FROM containercommidetail cc WHERE cc.containerid = '{0}' AND cc.workflowstepname = '{1}'", dr("containerId").ToString, dr("workflowstepName").ToString)
            If Not DatabaseHelper.GetSingle(sql) Is Nothing Then
                result.Result = False
                result.Message = String.Format("所选批次：{0}的工序：{1}已经外委，无法下发。" + dr("containerName").ToString, dr("workflowstepName").ToString)
                Exit For
            End If
            '热表验证是否存在工步
            If param("FactoryName").Contains("热表") Then
                sql = String.Format("select 1 from stepinfo si where si.workflowstepid = '{0}'", dr("workflowstepid"))
                If DatabaseHelper.GetSingle(sql) Is Nothing Then
                    result.Result = False
                    result.Message = "所选批次：" + dr("containerName").ToString + "的工序：" + dr("workflowstepName").ToString + " 不存在工步"
                    Exit For
                End If
            End If
            '判断工序是否同时下发正排倒排数据
            Dim drs = dt.Select(String.Format("containerId = '{0}' AND workflowstepid = '{1}'", dr("containerId").ToString, dr("workflowstepid").ToString)).CopyToDataTable()
            If drs.AsDataView().ToTable(True, "apstype").Rows.Count > 1 Then '判断是否存在相同工序下发
                result.Result = False
                result.Message = String.Format("所选批次：{0}的工序：{1}存在不同排产策略，请重新选择后下发", dr("containerName").ToString, dr("workflowstepName").ToString)
                Exit For
            End If
            '如果开始日期不足三天（包含今天），则不允许下发 add by zhangrj 20250723 注释20250724
            'If Not String.IsNullOrWhiteSpace(dr("starttime").ToString()) Then
            '    Dim dtThree = Date.Parse(String.Format("{0} 23:59:59", Date.Now.AddDays(2).ToShortDateString()))
            '    If Date.Parse(dr("starttime").ToString()) < dtThree Then
            '        result.Result = False
            '        result.Message = String.Format("所选批次：{0}的工序：{1}的排产开始日期不足三天，不允许下发", dr("containerName").ToString, dr("workflowstepName").ToString)
            '        Exit For
            '    End If
            'End If
        Next
        Return result
    End Function

    ''' <summary>
    ''' 验证是否可派工数据
    ''' </summary>
    ''' <param name="row"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function IsDispatchData(ByVal row As DataRow) As Boolean
        Dim sql As String = ""
        '验证工序是否转工
        sql = <string> SELECT ap.apsplandetailid FROM apsplandetail ap 
                    LEFT JOIN apsplaninfo ai ON ai.apsplaninfoid = ap.apsplaninfoid AND ai.isuse = 0
                    LEFT JOIN containercurrentinfo cci ON cci.containerid = ai.containerid 
                    WHERE ap.workflowstepid = '{0}' AND ai.containerid = '{1}' AND cci.originalfactoryid {2} ap.specfactoryid
              </string>
        sql = String.Format(sql, row("workflowstepId").ToString, row("containerId").ToString, "<>")
        Dim dtzg As DataTable = DatabaseHelper.Query_ReturnDatTable(sql.ToString())
        If dtzg.Rows.Count > 0 Then
            Return False
            Exit Function
        End If
        '验证工序是否外委
        sql = <string> SELECT ap.apsplandetailid FROM apsplandetail ap 
                    LEFT JOIN apsplaninfo ai ON ai.apsplaninfoid = ap.apsplaninfoid AND ai.isuse = 0
                    LEFT JOIN Workflowstep ws ON ws.workflowstepid = ap.workflowstepid 
                    WHERE ap.workflowstepid = '{0}' AND ai.containerid = '{1}' AND ws.controlcode = 'LY05'
              </string>
        sql = String.Format(sql, row("workflowstepId").ToString, row("containerId").ToString)
        Dim dtw As DataTable = DatabaseHelper.Query_ReturnDatTable(sql.ToString())
        If dtw.Rows.Count > 0 Then
            Return False
            Exit Function
        End If
        Return True
    End Function

    ''' <summary>
    ''' 验证回收数据
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function VaildRecall(ByVal dt As DataTable) As uMESProcessResult
        Dim result = New uMESProcessResult()
        result.Result = True
        Dim sqlAll = ""

        For Each dr As DataRow In dt.Rows
            Dim sql = String.Format("SELECT 1 FROM containercombinate cb WHERE cb.containerid = '{0}'", dr("containerId").ToString)
            If Not DatabaseHelper.GetSingle(sql) Is Nothing Then
                result.Result = False
                result.Message = "所选批次：" + dr("containerName").ToString + "已合卡，请删除合卡后再回收"
                Exit For
            End If
            Dim sqlStr As String = <string>
                                    SELECT c.containername FROM apsplandetail apd 
                                    LEFT JOIN apsplaninfo api ON api.apsplaninfoid = apd.apsplaninfoid
                                    LEFT JOIN container c ON c.containerid = api.containerid
                                    LEFT JOIN containerstepworkreport csr ON csr.containerid = api.containerid AND csr.workflowstepid = apd.workflowstepid
                                    WHERE csr.containerstepworkreportid IS NOT NULL and api.containerid = '{0}'
                                    AND apd.workflowstepid = '{1}' AND apd.isuse = 0 AND api.isuse = 0 AND csr.isuse = 0
                                   </string>.Value
            sqlStr = String.Format(sqlStr, dr("containerId").ToString, dr("workflowstepId").ToString)
            If String.IsNullOrWhiteSpace(sqlAll) Then
                sqlAll = sqlStr
            Else
                sqlAll += " UNION " + sqlStr
            End If
        Next

        If Not result.Result Then
            Return result
            Exit Function
        End If

        Dim dts As DataTable = DatabaseHelper.Query_ReturnDatTable(sqlAll.ToString)

        If dts.Rows.Count > 0 Then
            result.Result = False
            result.Message = "批次：" + dts.Rows(0)("containername").ToString + "已经报工，请重新查询"
        Else
            result.Result = True
        End If
        Return result
    End Function

    ''' <summary>
    ''' 验证删除数据
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function VaildDele(ByVal dt As DataTable) As uMESProcessResult
        Dim result = New uMESProcessResult()
        result.Result = True
        For Each dr As DataRow In dt.Rows
            Dim sql = String.Format("SELECT 1 FROM containercombinate cb WHERE cb.containerid = '{0}'", dr("containerId").ToString)
            If Not DatabaseHelper.GetSingle(sql) Is Nothing Then
                result.Result = False
                result.Message = "所选批次：" + dr("containerName").ToString + "已合卡，请删除合卡后再回收"
                Exit For
            End If
            sql = String.Format("SELECT 1 FROM apsplandetail apd WHERE apd.apsplandetailid = '{0}' AND apd.status = '1'", dr("apsplandetailid").ToString)
            If Not DatabaseHelper.GetSingle(sql) Is Nothing Then
                result.Result = False
                result.Message = "所选批次：" + dr("containerName").ToString + "已经存在下发数据，请重新查询"
                Exit For
            End If
        Next
        Return result
    End Function

    ''' <summary>
    ''' 删除排产回收后的派工数据
    ''' </summary>
    ''' <param name="dtAps"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function ApsRecallDispatch(ByVal dtAps As DataTable) As Boolean
        Dim dt As New DataTable
        Dim sqlStr As String = <string>
                                   SELECT csd.containerstepdispatchid FROM containerstepdispatch csd where csd.isuse=0 AND
                               </string>.Value
        Dim sqlCondition As String = ""
        Dim dtContainer As DataTable = dtAps.DefaultView.ToTable(True, "containerId", "workflowstepId")
        For Each dr As DataRow In dtContainer.Rows
            sqlCondition += String.Format(" (csd.containerid = '{0}' AND csd.workflowstepid = '{1}') or", dr("containerId").ToString, dr("workflowstepId").ToString)
        Next
        sqlCondition = "(" + sqlCondition.Trim("r").Trim("o") + ")"
        sqlStr = sqlStr + sqlCondition
        Dim dts As DataTable = DatabaseHelper.Query_ReturnDatTable(sqlStr.ToString)
        For Each drs As DataRow In dts.Rows
            sqlStr = String.Format("UPDATE CONTAINERSTEPDISPATCH C SET C.ISUSE=1 WHERE C.CONTAINERSTEPDISPATCHID = '{0}'", drs("containerStepDispatchID").ToString)
            Dim rows As Integer = DatabaseHelper.ExecuteSql(sqlStr)
            If (rows > 0) Then
                If Not (GetAndDoWithDispatchDelInfo(drs("containerStepDispatchID").ToString)) Then
                    Return False
                    Exit Function
                End If
            End If
        Next
        Return True
    End Function

    ''' <summary>
    ''' yuxj20220826删除排产回收后的派工数据
    ''' </summary>
    ''' <param name="dtAps"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function ApsRecallDispatchStepinfo(ByVal dtAps As DataTable) As Boolean
        Dim dt As New DataTable
        Dim sqlStr As String = <string>
                                   SELECT csd.containerstepdispatchid FROM containerstepdispatch2 csd where csd.isuse=0 AND
                               </string>.Value
        Dim sqlCondition As String = ""
        Dim dtContainer As DataTable = dtAps.DefaultView.ToTable(True, "containerId", "workflowstepId")
        For Each dr As DataRow In dtContainer.Rows
            sqlCondition += String.Format(" (csd.containerid = '{0}' AND csd.workflowstepid = '{1}') or", dr("containerId").ToString, dr("workflowstepId").ToString)
        Next
        sqlCondition = "(" + sqlCondition.Trim("r").Trim("o") + ")"
        sqlStr = sqlStr + sqlCondition
        Dim dts As DataTable = DatabaseHelper.Query_ReturnDatTable(sqlStr.ToString)
        For Each drs As DataRow In dts.Rows
            sqlStr = String.Format("UPDATE CONTAINERSTEPDISPATCH2 C SET C.ISUSE=1 WHERE C.CONTAINERSTEPDISPATCHID = '{0}'", drs("containerStepDispatchID").ToString)
            Dim rows As Integer = DatabaseHelper.ExecuteSql(sqlStr)
            If (rows > 0) Then
                If Not (GetAndDoWithDispatchDelInfo2(drs("containerStepDispatchID").ToString)) Then
                    Return False
                    Exit Function
                End If
            End If
        Next
        Return True
    End Function


    ''' <summary>
    ''' yuxj20220826获取并处理删除的批次记录逻辑
    ''' </summary>
    ''' <param name="containerStepDispatchID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetAndDoWithDispatchDelInfo2(ByVal containerStepDispatchID As String) As Boolean
        Dim conCurrObjectBus As New uLMSContainercurrentinfoBusiness
        Dim dt As DataTable = conCurrObjectBus.GetDelDispatchInfo2(containerStepDispatchID)
        If dt.Rows.Count = 0 Then
            Return True
            Exit Function
        End If
        Dim dr As DataRow = dt.Rows(0)
        Dim otherdt As DataTable = conCurrObjectBus.GetContainerOtherDispatchInfo2(dr("CONTAINERID"), Integer.Parse(dr("WORKFLOWSTEPSEQUENCE")))
        Dim para = New Dictionary(Of String, String)
        '如果没有其他派工记录，则将DisPatchNum和IsDisPatch置为0
        If otherdt.Rows.Count = 0 Then
            para.Add("dispatchNum", "0")
            para.Add("isDispatch", "0")
            para.Add("containerID", dr("CONTAINERID").ToString())
        Else
            For Each r As DataRow In otherdt.Rows
                '如果存在当前序派工记录，则不用再更新状态（原来批次派工标识就是 IsDisPatch=1 DisPatchNum=1）
                If r("WORKFLOWSTEPID") = dr("WORKFLOWSTEPID") Then
                    Return True
                    Exit Function
                End If
            Next
            '存在派工，但是当前序不存在派工
            para.Add("dispatchNum", "1")
            para.Add("isDispatch", "0")
            para.Add("containerID", dr("CONTAINERID").ToString())
        End If
        '更新批次服务总表派工状态
        Dim strsql As New StringBuilder
        strsql.AppendLine("UPDATE CONTAINERCURRENTINFO C SET")
        strsql.AppendFormat(" C.DISPATCHNUM ={0},", para("dispatchNum"))
        strsql.AppendFormat(" C.Isdispatch ={0}", para("isDispatch"))
        strsql.AppendFormat(" WHERE C.CONTAINERID='{0}'", para("containerID"))
        If DatabaseHelper.ExecuteSql(strsql.ToString()) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function


    ''' <summary>
    ''' 获取并处理删除的批次记录逻辑
    ''' </summary>
    ''' <param name="containerStepDispatchID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetAndDoWithDispatchDelInfo(ByVal containerStepDispatchID As String) As Boolean
        Dim conCurrObjectBus As New uLMSContainercurrentinfoBusiness
        Dim dt As DataTable = conCurrObjectBus.GetDelDispatchInfo(containerStepDispatchID)
        If dt.Rows.Count = 0 Then
            Return True
            Exit Function
        End If
        Dim dr As DataRow = dt.Rows(0)
        Dim otherdt As DataTable = conCurrObjectBus.GetContainerOtherDispatchInfo(dr("CONTAINERID"), Integer.Parse(dr("WORKFLOWSTEPSEQUENCE")))
        Dim para = New Dictionary(Of String, String)
        '如果没有其他派工记录，则将DisPatchNum和IsDisPatch置为0
        If otherdt.Rows.Count = 0 Then
            para.Add("dispatchNum", "0")
            para.Add("isDispatch", "0")
            para.Add("containerID", dr("CONTAINERID").ToString())
        Else
            For Each r As DataRow In otherdt.Rows
                '如果存在当前序派工记录，则不用再更新状态（原来批次派工标识就是 IsDisPatch=1 DisPatchNum=1）
                If r("WORKFLOWSTEPID") = dr("WORKFLOWSTEPID") Then
                    Return True
                    Exit Function
                End If
            Next
            '存在派工，但是当前序不存在派工
            para.Add("dispatchNum", "1")
            para.Add("isDispatch", "0")
            para.Add("containerID", dr("CONTAINERID").ToString())
        End If
        '更新批次服务总表派工状态
        Dim strsql As New StringBuilder
        strsql.AppendLine("UPDATE CONTAINERCURRENTINFO C SET")
        strsql.AppendFormat(" C.DISPATCHNUM ={0},", para("dispatchNum"))
        strsql.AppendFormat(" C.Isdispatch ={0}", para("isDispatch"))
        strsql.AppendFormat(" WHERE C.CONTAINERID='{0}'", para("containerID"))
        If DatabaseHelper.ExecuteSql(strsql.ToString()) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 排产下发后的自动派工
    ''' add:Wangjh 20220706
    ''' </summary>
    ''' <param name="apsDt">需要派工的排产信息</param> 
    ''' <param name="para">通用参数</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function ApsAutoDispatch(ByVal apsDt As DataTable, ByVal para As Dictionary(Of String, String)) As ArrayList
        Dim re = New uMESProcessResult
        re.Result = False

        Dim idsCondition = ""
        For Each row In apsDt.Rows
            If IsDispatchData(row) Then '判断是否可派工
                idsCondition += " ad.ApsPlanDetailID='" + row("ApsPlanDetailID").ToString + "' or"
            End If
        Next
        idsCondition = idsCondition.Trim("r").Trim("o")
        'yuxj20221101 判断为空时，不需要派工
        If idsCondition = "" Then
            Return New ArrayList
        End If

        Dim apsSql = <string>select c.qty,ad.qty apsqty,w.factoryid wffactoryid,c.workflowstepid cuworkflowstepid,ad.* from ApsPlanDetail ad 
                            left join ApsPlanInfo ai on ai.ApsPlanInfoID=ad.ApsPlanInfoID
                            left join containercurrentinfo c on c.containerid=ai.containerid 
                            left join workflow w on w.workflowid=ai.workflowid
                            left join workflowstep ws on ws.workflowstepid=ad.workflowstepid
                            where w.factoryid=ad.SpecFactoryID and ad.status=1 and nvl(ws.controlcode,'0')!='LY05' </string>.Value

        apsSql += " and (" + idsCondition + " ) and ad.isuse=0 and ad.Status=1 "

        apsSql += " order by ai.containerid,ad.workflowstepid "

        Dim apsEmpSql = "select * from ApsPlanDetailEmp ad where 1=1 and (" + idsCondition + " )" '排产的人员

        Dim apsDetailDt = DatabaseHelper.Query_ReturnDatTable(apsSql) '所有排产的记录
        Dim apsEmpDt = DatabaseHelper.Query_ReturnDatTable(apsEmpSql) '所有排产的人员

        '派工相关sql
        Dim selectDispatchSql = <string>select distinct csd.* from containerstepdispatch csd 
                                LEFT JOIN apsplaninfo ai ON ai.containerid = csd.containerid AND ai.isuse = 0
                                left join apsplandetail ad on ad.apsplaninfoid = ai.apsplaninfoid AND ad.workflowstepid = csd.workflowstepid
                                where csd.isuse=0 </string>.Value
        Dim apsDispatchDt = DatabaseHelper.Query_ReturnDatTable(selectDispatchSql + "and (" + idsCondition + " )") '已经派工的数据

        Dim dispatchSql = <string>INSERT INTO CONTAINERSTEPDISPATCH(SourceType,CONTAINERID,FACTORYID,WORKCENTERID,TEAMID,WORKFLOWSTEPID,RESOURCEID,WORKERID,QTY,PLANNEDSTARTDATE,PLANNEDFINISHDATE,CONTAINERSTEPDISPATCHID,EMPLOYEEID,CREATEDATE,ISUSE,ISTRANSFER,PRIORITYCODE,STEPINFOID)
VALUES(1,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',to_date('{8}','yyyy/mm/dd hh24:mi:ss'),to_date('{9}','yyyy/mm/dd hh24:mi:ss'),'{10}','{11}',sysdate,0,0,'{12}','{13}')</string>.Value

        Dim dispatchEmpSql = <string>begin delete CONTAINERSTEPDISPATCHDETAIL where CONTAINERSTEPDISPATCHID='{0}' and WORKERID='{1}';
INSERT INTO CONTAINERSTEPDISPATCHDETAIL(CONTAINERSTEPDISPATCHID,WORKERID,CreateDate) VALUES('{0}','{1}',sysdate); end;</string>.Value

        Dim containerDispatchSql = "UPDATE containercurrentinfo cu SET cu.dispatchnum = 1,cu.isdispatch ={0} where cu.containerid='{1}'"

        Dim resEmpSql = "SELECT el.resourcelistid resourceid,el.employeeid FROM employeeresourcelist el where el.resourcelistid='{0}'"

        Dim sqlList As New ArrayList

        Dim apsContainerDt As DataTable = apsDetailDt.DefaultView.ToTable(True, "containerid")
        For Each containerRow In apsContainerDt.Rows
            'sqlList.Clear()
            Dim _apsDetailDt = apsDetailDt.Select("containerid='" + containerRow("containerid").ToString + "'").CopyToDataTable()
            Dim isCludeCurrent = False '自动派工中是否包含当前序
            For Each row As DataRow In _apsDetailDt.Rows
                If apsDispatchDt.Select(String.Format("containerid='{0}' and workflowstepid='{1}'", row("ContainerID").ToString, row("WORKFLOWSTEPID").ToString)).Length > 0 Then '已有派工记录
                    Continue For
                End If

                Dim someStep = _apsDetailDt.Select(String.Format("workflowstepid = '{0}' and apsplandetailid <> '{1}'", row("WORKFLOWSTEPID").ToString, row("apsplandetailid").ToString))
                If someStep.Length > 0 Then
                    If Integer.Parse(row("apsqty").ToString()) >= Integer.Parse(row("qty").ToString()) Then '如果本工序的排产数大于等于批次数，则删除掉同工序排产数据，不派工
                        For Each item As DataRow In someStep
                            _apsDetailDt.Rows.Remove(item)
                        Next
                        _apsDetailDt.AcceptChanges()
                    End If
                End If


                If row("cuworkflowstepid") = row("WORKFLOWSTEPID") Then
                    isCludeCurrent = True
                End If
                Dim _apsEmpDrs = apsEmpDt.Select("ApsPlanDetailID='" + row("ApsPlanDetailID").ToString + "'")

                Dim dispatchId = Guid.NewGuid.ToString

                If _apsEmpDrs.Length = 1 Then
                    sqlList.Add(String.Format(dispatchSql, row("ContainerID"), row("SpecFactoryID"), row("WORKCENTERID"), row("TEAMID"), row("WORKFLOWSTEPID"), row("RESOURCEID"), _apsEmpDrs(0)("EmployeeID"),
                                              row("apsqty").ToString, row("starttime").ToString, row("endtime").ToString, dispatchId, para("EmployeeID"), "1", ""))
                Else
                    sqlList.Add(String.Format(dispatchSql, row("ContainerID"), row("SpecFactoryID"), row("WORKCENTERID"), row("TEAMID"), row("WORKFLOWSTEPID"), row("RESOURCEID"), "",
                                          row("apsqty").ToString, row("starttime").ToString, row("endtime").ToString, dispatchId, para("EmployeeID"), "1", ""))

                End If

                'If _apsEmpDrs.Length = 0 Then
                '    Continue For
                'End If

                If _apsEmpDrs.Length > 1 Then '派给多人
                    For Each apsEmpRow As DataRow In _apsEmpDrs
                        sqlList.Add(String.Format(dispatchEmpSql, dispatchId, apsEmpRow("EmployeeID").ToString))
                    Next
                ElseIf _apsEmpDrs.Length = 0 Then '未排产到人，自动派工时派给所有人
                    Dim resEmpDt = DatabaseHelper.Query_ReturnDatTable(String.Format(resEmpSql, row("RESOURCEID").ToString()))
                    For Each resEmpRow As DataRow In resEmpDt.Rows
                        sqlList.Add(String.Format(dispatchEmpSql, dispatchId, resEmpRow("EmployeeID").ToString))
                    Next
                End If

            Next
            If isCludeCurrent Then
                sqlList.Add(String.Format(containerDispatchSql, 1, containerRow("containerid").ToString))
            Else
                sqlList.Add(String.Format(containerDispatchSql, 0, containerRow("containerid").ToString))
            End If

            'DatabaseHelper.ExecuteSqlListTran(sqlList)
        Next

        re.Result = True

        Return sqlList '更改为返回sql列表，edit by zhangrj 2022-10-31
        'Return re
    End Function

    ''' <summary>
    ''' 排产下发后的自动派工,派工到工步
    ''' add:yuxj 20220825
    ''' </summary>
    ''' <param name="apsDt">需要派工的排产信息</param> 
    ''' <param name="para">通用参数</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function ApsAutoDispatchStepInfo(ByVal apsDt As DataTable, ByVal para As Dictionary(Of String, String)) As ArrayList
        Dim re = New uMESProcessResult
        re.Result = False

        Dim idsCondition = ""
        For Each row In apsDt.Rows
            If IsDispatchData(row) Then '判断是否可派工
                idsCondition += " ad.ApsPlanDetailID='" + row("ApsPlanDetailID").ToString + "' or"
            End If
        Next
        idsCondition = idsCondition.Trim("r").Trim("o")
        'yuxj20221101 判断为空时，不需要派工
        If idsCondition = "" Then
            Return New ArrayList
        End If
        Dim apsSql As String = <string>
                                select distinct si.stepinfoid, c.qty,ad.qty apsqty,w.factoryid wffactoryid,c.workflowstepid cuworkflowstepid,ad.* from ApsPlanDetail ad 
                                left join ApsPlanInfo ai on ai.ApsPlanInfoID=ad.ApsPlanInfoID
                                left join containercurrentinfo c on c.containerid=ai.containerid 
                                left join workflow w on w.workflowid=ai.workflowid
                                left join workflowstep ws on ws.workflowstepid=ad.workflowstepid
                                left join stepinfo si on si.workflowstepid=ws.workflowstepid
                                LEFT JOIN containerstepdispatch2 csd ON csd.containerid = c.containerid AND csd.workflowstepid = ws.workflowstepid AND csd.isuse = 0
                                LEFT JOIN stepinfo s1 ON s1.stepinfoid = csd.stepinfoid
                                LEFT JOIN stepinfobase sb ON sb.revofrcdid = si.Stepinfoid --取最新版
                                LEFT JOIN stepinfo s2 ON s2.stepinfoid = sb.revofrcdid
                                where w.factoryid=ad.SpecFactoryID and ad.status=1 and nvl(ws.controlcode,'0')!='LY05' 
                                AND (CASE WHEN csd.containerstepdispatchid IS NULL THEN s2.revision ELSE s1.Revision END) = si.revision
                                </string>

        apsSql += " and (" + idsCondition + " ) and ad.isuse=0 and ad.Status=1 "

        'apsSql += " order by ai.containerid,ad.workflowstepid "

        Dim apsEmpSql = "select * from ApsPlanDetailEmp ad where 1=1 and (" + idsCondition + " )" '排产的人员

        Dim apsDetailDt = DatabaseHelper.Query_ReturnDatTable(apsSql) '所有排产的记录
        Dim apsEmpDt = DatabaseHelper.Query_ReturnDatTable(apsEmpSql) '所有排产的人员

        '工步派工相关sql
        Dim selectDispatchSql = <string>select distinct csd.* from containerstepdispatch2 csd 
                                left join ApsPlanInfo ai on ai.containerid=csd.containerid AND ai.isuse = 0
                                left join apsplandetail ad on ad.apsplaninfoid = ai.apsplaninfoid and ad.workflowstepid = csd.workflowstepid
                                where csd.isuse=0</string>.Value
        Dim apsDispatchDt = DatabaseHelper.Query_ReturnDatTable(selectDispatchSql + " and (" + idsCondition + " )") '已经派工的数据

        Dim dispatchSql = <string>INSERT INTO CONTAINERSTEPDISPATCH2(SourceType,CONTAINERID,FACTORYID,WORKCENTERID,TEAMID,WORKFLOWSTEPID,RESOURCEID,WORKERID,QTY,PLANNEDSTARTDATE,PLANNEDFINISHDATE,CONTAINERSTEPDISPATCHID,EMPLOYEEID,CREATEDATE,ISUSE,ISTRANSFER,PRIORITYCODE,STEPINFOID)
VALUES(1,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',to_date('{8}','yyyy/mm/dd hh24:mi:ss'),to_date('{9}','yyyy/mm/dd hh24:mi:ss'),'{10}','{11}',sysdate,0,0,'{12}','{13}')</string>.Value

        Dim dispatchEmpSql = <string>begin delete CONTAINERSTEPDISPATCHDETAIL2 where CONTAINERSTEPDISPATCHID='{0}' and WORKERID='{1}';
INSERT INTO CONTAINERSTEPDISPATCHDETAIL2(CONTAINERSTEPDISPATCHID,WORKERID,CreateDate) VALUES('{0}','{1}',sysdate); end;</string>.Value

        '工序派工相关sql
        Dim selectDispatchSql1 = <string>select distinct csd.* from containerstepdispatch csd 
                                LEFT JOIN apsplaninfo ai ON ai.containerid = csd.containerid AND ai.isuse = 0
                                left join apsplandetail ad on ad.apsplaninfoid = ai.apsplaninfoid AND ad.workflowstepid = csd.workflowstepid
                                where csd.isuse=0 </string>.Value
        Dim apsDispatchDt1 = DatabaseHelper.Query_ReturnDatTable(selectDispatchSql1 + " and (" + idsCondition + " )") '已经派工的数据

        Dim dispatchSql1 = <string>INSERT INTO CONTAINERSTEPDISPATCH(SourceType,CONTAINERID,FACTORYID,WORKCENTERID,TEAMID,WORKFLOWSTEPID,RESOURCEID,WORKERID,QTY,PLANNEDSTARTDATE,PLANNEDFINISHDATE,CONTAINERSTEPDISPATCHID,EMPLOYEEID,CREATEDATE,ISUSE,ISTRANSFER,PRIORITYCODE,STEPINFOID)
VALUES(1,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',to_date('{8}','yyyy/mm/dd hh24:mi:ss'),to_date('{9}','yyyy/mm/dd hh24:mi:ss'),'{10}','{11}',sysdate,0,0,'{12}','{13}')</string>.Value

        Dim dispatchEmpSql1 = <string>begin delete CONTAINERSTEPDISPATCHDETAIL where CONTAINERSTEPDISPATCHID='{0}' and WORKERID='{1}';
INSERT INTO CONTAINERSTEPDISPATCHDETAIL(CONTAINERSTEPDISPATCHID,WORKERID,CreateDate) VALUES('{0}','{1}',sysdate); end;</string>.Value
        '------
        Dim containerDispatchSql = "UPDATE containercurrentinfo cu SET cu.dispatchnum = 1,cu.isdispatch ={0} where cu.containerid='{1}'"

        Dim resEmpSql = "SELECT el.resourcelistid resourceid,el.employeeid FROM employeeresourcelist el where el.resourcelistid='{0}'"

        Dim sqlList As New ArrayList
        Dim disList As New List(Of String)

        Dim apsContainerDt As DataTable = apsDetailDt.DefaultView.ToTable(True, "containerid")
        For Each containerRow In apsContainerDt.Rows
            disList.Clear()
            Dim _apsDetailDt = apsDetailDt.Select("containerid='" + containerRow("containerid").ToString + "'")
            Dim isCludeCurrent = False '自动派工中是否包含当前序
            For Each row As DataRow In _apsDetailDt
                If apsDispatchDt.Select(String.Format("containerid='{0}' and workflowstepid='{1}' and stepinfoid='{2}'", row("ContainerID").ToString, row("WORKFLOWSTEPID").ToString, row("StepInfoID").ToString)).Length > 0 Then '已有派工记录
                    Continue For
                End If
                '如果存在相同的工步派工，则不添加
                Dim dic As String = row("ContainerID").ToString() + row("WORKFLOWSTEPID").ToString() + row("stepinfoid").ToString()
                If disList.Contains(dic) Then
                    Continue For
                End If
                disList.Add(dic)
                'If row("cuworkflowstepid") = row("WORKFLOWSTEPID") Then
                '    isCludeCurrent = True
                'End If
                Dim _apsEmpDrs = apsEmpDt.Select("ApsPlanDetailID='" + row("ApsPlanDetailID").ToString + "'")

                Dim dispatchId = Guid.NewGuid.ToString
                If _apsEmpDrs.Length = 1 Then
                    sqlList.Add(String.Format(dispatchSql, row("ContainerID"), row("SpecFactoryID"), row("WORKCENTERID"), row("TEAMID"), row("WORKFLOWSTEPID"), row("RESOURCEID"), _apsEmpDrs(0)("EmployeeID"),
                                         row("apsqty").ToString, row("starttime").ToString, row("endtime").ToString, dispatchId, para("EmployeeID"), "1", row("stepinfoid"))) '保存工步
                Else
                    sqlList.Add(String.Format(dispatchSql, row("ContainerID"), row("SpecFactoryID"), row("WORKCENTERID"), row("TEAMID"), row("WORKFLOWSTEPID"), row("RESOURCEID"), "",
                                          row("apsqty").ToString, row("starttime").ToString, row("endtime").ToString, dispatchId, para("EmployeeID"), "1", row("stepinfoid"))) '保存工步
                End If

                If _apsEmpDrs.Length > 1 Then '派给多人
                    For Each apsEmpRow As DataRow In _apsEmpDrs
                        sqlList.Add(String.Format(dispatchEmpSql, dispatchId, apsEmpRow("EmployeeID").ToString))
                    Next
                ElseIf _apsEmpDrs.Length = 0 Then '未排产到人，自动派工时派给所有人
                    Dim resEmpDt = DatabaseHelper.Query_ReturnDatTable(String.Format(resEmpSql, row("RESOURCEID").ToString()))
                    For Each resEmpRow As DataRow In resEmpDt.Rows
                        sqlList.Add(String.Format(dispatchEmpSql, dispatchId, resEmpRow("EmployeeID").ToString))
                    Next
                End If
            Next
            disList.Clear()
            '查询出当前批次下所有的工序,并派工
            Dim dtWorkflowstep = _apsDetailDt.CopyToDataTable().DefaultView().ToTable(True, "ContainerID", "WORKFLOWSTEPID")
            For Each drw As DataRow In dtWorkflowstep.Rows
                Dim row As DataRow = _apsDetailDt.CopyToDataTable().Select("containerid='" + drw("ContainerID").ToString + "' and workflowstepid='" + drw("WORKFLOWSTEPID").ToString + "'").FirstOrDefault()
                If apsDispatchDt1.Select(String.Format("containerid='{0}' and workflowstepid='{1}'", row("ContainerID").ToString, row("WORKFLOWSTEPID").ToString)).Length > 0 Then '已有派工记录
                    Continue For
                End If
                '如果存在相同的工序派工，则不添加
                Dim dic As String = row("ContainerID").ToString() + row("WORKFLOWSTEPID").ToString()
                If disList.Contains(dic) Then
                    Continue For
                End If
                disList.Add(dic)

                If row("cuworkflowstepid") = row("WORKFLOWSTEPID") Then
                    isCludeCurrent = True
                End If
                Dim _apsEmpDrs1 = apsEmpDt.Select("ApsPlanDetailID='" + row("ApsPlanDetailID").ToString + "'")

                Dim dispatchId1 = Guid.NewGuid.ToString

                If _apsEmpDrs1.Length = 1 Then
                    sqlList.Add(String.Format(dispatchSql1, row("ContainerID"), row("SpecFactoryID"), row("WORKCENTERID"), row("TEAMID"), row("WORKFLOWSTEPID"), row("RESOURCEID"), _apsEmpDrs1(0)("EmployeeID"),
                                         row("qty").ToString, row("starttime").ToString, row("endtime").ToString, dispatchId1, para("EmployeeID"), "1", ""))
                Else
                    sqlList.Add(String.Format(dispatchSql1, row("ContainerID"), row("SpecFactoryID"), row("WORKCENTERID"), row("TEAMID"), row("WORKFLOWSTEPID"), row("RESOURCEID"), "",
                                         row("qty").ToString, row("starttime").ToString, row("endtime").ToString, dispatchId1, para("EmployeeID"), "1", ""))
                End If

                If _apsEmpDrs1.Length > 1 Then '派给多人
                    For Each apsEmpRow As DataRow In _apsEmpDrs1
                        sqlList.Add(String.Format(dispatchEmpSql1, dispatchId1, apsEmpRow("EmployeeID").ToString))
                    Next
                ElseIf _apsEmpDrs1.Length = 0 Then '未排产到人，自动派工时派给所有人
                    Dim resEmpDt = DatabaseHelper.Query_ReturnDatTable(String.Format(resEmpSql, row("RESOURCEID").ToString()))
                    For Each resEmpRow As DataRow In resEmpDt.Rows
                        sqlList.Add(String.Format(dispatchEmpSql1, dispatchId1, resEmpRow("EmployeeID").ToString))
                    Next
                End If
            Next
            If isCludeCurrent Then
                sqlList.Add(String.Format(containerDispatchSql, 1, containerRow("containerid").ToString))
            Else
                sqlList.Add(String.Format(containerDispatchSql, 0, containerRow("containerid").ToString))
            End If

            'DatabaseHelper.ExecuteSqlListTran(sqlList)
        Next

        re.Result = True

        Return sqlList '更改为返回sql列表，edit by zhangrj 2022-10-31
        'Return re
    End Function

    ''' <summary>
    ''' 排产下发后的自动生产准备申请 add by zhangrj 2022-10-31
    ''' </summary>
    ''' <param name="apsDt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function ApsAutoProductPreparationInfo(ByVal apsDt As DataTable, ByVal para As Dictionary(Of String, String)) As ArrayList
        Dim sqlList As New ArrayList
        Dim arrangementsID2 = Guid.NewGuid.ToString() '生产准备ID2，一次申请，所有数据保持一致
        Dim dtWorkflowstep = apsDt.DefaultView.ToTable(True, "ContainerID", "WorkflowstepID") '去重，防止工序多次申请
        For Each drstep As DataRow In dtWorkflowstep.Rows
            Dim dr As DataRow = apsDt.Select(String.Format("containerid='{0}' and workflowstepid='{1}'", drstep("ContainerID").ToString, drstep("WorkflowstepID").ToString)).First()
            Dim sql As String = <string>
--夹模具表
SELECT WFS.WORKFLOWSTEPNAME,WFST.WORKFLOWSTEPID, WFST.QTY, WFST.SEQUENCE,ST.SPECIALTOOLID TOOLID, 
ST.SPECIALTOOLNAME TOOLNAME,ST.DESCRIPTION
  FROM WORKFLOWSTEPTOOLS WFST
  LEFT JOIN WORKFLOWSTEP WFS  ON WFS.WORKFLOWSTEPID = WFST.WORKFLOWSTEPID
  LEFT JOIN SPECIALTOOL ST  ON ST.SPECIALTOOLID = WFST.TOOLS
  LEFT JOIN ARRANGEMENTS A  ON A.WORKFLOWSTEPID = WFST.WORKFLOWSTEPID AND A.TOOLNO = ST.SPECIALTOOLNAME AND A.CONTAINERID = '{0}' and A.STEPINFOID IS NULL
 WHERE ST.SPECIALTOOLID {1} ' ' AND WFST.WORKFLOWSTEPID = '{2}' AND A.ARRANGEMENTSID IS NULL
UNION ALL
--刀具
SELECT WFS.WORKFLOWSTEPNAME,WFST.WORKFLOWSTEPID, WFST.QTY, WFST.SEQUENCE,CT.CUTTOOLID TOOLID, 
CT.CUTTOOLNAME TOOLNAME,CT.DESCRIPTION
  FROM WORKFLOWSTEPTOOLS WFST
  LEFT JOIN WORKFLOWSTEP WFS  ON WFS.WORKFLOWSTEPID = WFST.WORKFLOWSTEPID
  LEFT JOIN CUTTOOL CT  ON CT.CUTTOOLID = WFST.TOOLS
  LEFT JOIN ARRANGEMENTS A  ON A.WORKFLOWSTEPID = WFST.WORKFLOWSTEPID AND A.TOOLNO = CT.CUTTOOLNAME AND A.CONTAINERID = '{0}' and A.STEPINFOID IS NULL
   WHERE CT.CUTTOOLID {1} ' ' AND WFST.WORKFLOWSTEPID = '{2}' AND A.ARRANGEMENTSID IS NULL
UNION ALL
--量具表
SELECT WFS.WORKFLOWSTEPNAME,WFST.WORKFLOWSTEPID, WFST.QTY, WFST.SEQUENCE,MT.MEASURETOOLID TOOLID, 
MT.MEASURETOOLNAME TOOLNAME,MT.DESCRIPTION
  FROM WORKFLOWSTEPTOOLS WFST
  LEFT JOIN WORKFLOWSTEP WFS  ON WFS.WORKFLOWSTEPID = WFST.WORKFLOWSTEPID
  LEFT JOIN MEASURETOOL MT  ON MT.MEASURETOOLID = WFST.TOOLS
  LEFT JOIN ARRANGEMENTS A  ON A.WORKFLOWSTEPID = WFST.WORKFLOWSTEPID AND A.TOOLNO = MT.MEASURETOOLNAME AND A.CONTAINERID = '{0}' and A.STEPINFOID IS NULL
   WHERE MT.MEASURETOOLID {1} ' ' AND WFST.WORKFLOWSTEPID ='{2}' AND A.ARRANGEMENTSID IS NULL
                            </string>.Value
            sql = String.Format(sql, dr("ContainerID").ToString, "<>", dr("WorkflowstepID").ToString)
            '查询出工序工装工具是否申请
            Dim dt = DatabaseHelper.Query_ReturnDatTable(sql)
            Dim dic As New Dictionary(Of String, String)
            '如果存在没有申请的工序，则申请
            If dt.Rows.Count > 0 Then
                For Each item As DataRow In dt.Rows
                    dic.Clear()
                    dic.Add("ContainerID", dr("ContainerID").ToString)
                    dic.Add("QTY", item("QTY").ToString)
                    dic.Add("ToolName", item("DESCRIPTION").ToString)
                    dic.Add("ToolNo", item("TOOLNAME").ToString)
                    dic.Add("ToolType", "LY14")
                    dic.Add("EmployeeID", para("EmployeeID"))
                    dic.Add("MfgOrderName", dr("MfgOrderName").ToString)
                    dic.Add("TeamID", para("TeamID"))
                    dic.Add("WorkflowstepID", dr("WorkflowstepID").ToString)
                    dic.Add("ArrangementsID2", arrangementsID2)
                    dic.Add("WorkflowID", dr("WorkflowID").ToString)
                    dic.Add("ContainerType", dr("ContainerType").ToString)
                    dic.Add("StepInfoID", "")
                    dic.Add("ApplyType", "0")
                    sqlList.AddRange(GetAddPreparationInfoSql(dic))
                Next
            End If
            '查询工序辅料是否申请
            sql = <string>
SELECT wst.WORKFLOWSTEPID, wst.QTY, wst.SEQUENCE,am.auxmaterialid,am.auxmaterialname,
am.description,am.specification,am.vendormodel
 FROM Workflowsteptools wst
LEFT JOIN workflowstep w  ON w.workflowstepid = wst.workflowstepid
inner join auxmaterial am on am.auxmaterialid = wst.tools
LEFT JOIN ARRANGEMENTS A  ON A.WORKFLOWSTEPID =w.workflowstepid AND A.TOOLNO = am.auxmaterialname AND A.CONTAINERID = '{0}' and A.STEPINFOID IS NULL
WHERE w.workflowstepid = '{1}' AND A.Arrangementsid IS NULL 
                  </string>.Value
            sql = String.Format(sql, dr("ContainerID").ToString, dr("WorkflowstepID").ToString)
            Dim dtaux = DatabaseHelper.Query_ReturnDatTable(sql)
            '如果存在未申请的辅料
            If dtaux.Rows.Count > 0 Then
                For Each item As DataRow In dtaux.Rows
                    dic.Clear()
                    dic.Add("ContainerID", dr("ContainerID").ToString)
                    dic.Add("QTY", item("QTY").ToString)
                    dic.Add("ToolName", item("DESCRIPTION").ToString)
                    dic.Add("ToolNo", item("auxmaterialname").ToString)
                    dic.Add("ToolType", "LY14")
                    dic.Add("EmployeeID", para("EmployeeID"))
                    dic.Add("MfgOrderName", dr("MfgOrderName").ToString)
                    dic.Add("TeamID", para("TeamID"))
                    dic.Add("WorkflowstepID", dr("WorkflowstepID").ToString)
                    dic.Add("ArrangementsID2", arrangementsID2)
                    dic.Add("WorkflowID", dr("WorkflowID").ToString)
                    dic.Add("ContainerType", dr("ContainerType").ToString)
                    dic.Add("StepInfoID", "")
                    dic.Add("ApplyType", "5")
                    sqlList.AddRange(GetAddPreparationInfoSql(dic))
                Next
            End If
            If para("FactoryName").Contains("热表") Then
                '添加工步工装工具/辅料的生产准备
                sqlList.AddRange(ApsAutoProductPreparationInfoByStepInfo(dr, para, arrangementsID2))
            End If
        Next
        Return sqlList
    End Function

    ''' <summary>
    ''' 排产下发后的自动生产准备申请（工步） add by zhangrj 2022-10-31
    ''' </summary>
    ''' <param name="stepDr">工序</param>
    ''' <param name="para">其他参数</param>
    ''' <param name="arrangementsID2">生产准备ID2（一次申请所有id2保持一致）</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function ApsAutoProductPreparationInfoByStepInfo(ByVal stepDr As DataRow, ByVal para As Dictionary(Of String, String), ByVal arrangementsID2 As String) As ArrayList
        Dim sqlList As New ArrayList
        Dim sql As String = <string>

--夹模具表
SELECT sit.stepinfoid,s.description stepdesc, sit.qty, sit.sequence,ST.SPECIALTOOLID TOOLID, 
ST.SPECIALTOOLNAME TOOLNAME,ST.DESCRIPTION
  FROM stepinfotools sit
  LEFT JOIN stepinfo s  ON s.stepinfoid = sit.stepinfoid
  LEFT JOIN SPECIALTOOL ST  ON ST.SPECIALTOOLID = sit.tools
  LEFT JOIN ARRANGEMENTS A  ON A.WORKFLOWSTEPID = '{0}' AND A.TOOLNO = ST.SPECIALTOOLNAME AND A.CONTAINERID = '{1}' and A.STEPINFOID IS NOT NULL
 WHERE ST.SPECIALTOOLID {2} ' ' AND s.workflowstepid = '{0}' AND A.ARRANGEMENTSID IS NULL
UNION ALL
--刀具
SELECT sit.stepinfoid,s.description stepdesc, sit.qty, sit.sequence,CT.CUTTOOLID TOOLID, 
CT.CUTTOOLNAME TOOLNAME,CT.DESCRIPTION
  FROM stepinfotools sit
  LEFT JOIN stepinfo s  ON s.stepinfoid = sit.stepinfoid
  LEFT JOIN CUTTOOL CT  ON CT.CUTTOOLID = sit.tools
  LEFT JOIN ARRANGEMENTS A  ON A.WORKFLOWSTEPID = '{0}' AND A.TOOLNO = CT.CUTTOOLNAME AND A.CONTAINERID = '{1}' and A.STEPINFOID IS NOT NULL
 WHERE CT.CUTTOOLID {2} ' ' AND s.workflowstepid = '{0}' AND A.ARRANGEMENTSID IS NULL
UNION ALL
--量具表
SELECT sit.stepinfoid,s.description stepdesc, sit.qty, sit.sequence,MT.MEASURETOOLID TOOLID, 
MT.MEASURETOOLNAME TOOLNAME,MT.DESCRIPTION
  FROM stepinfotools sit
  LEFT JOIN stepinfo s  ON s.stepinfoid = sit.stepinfoid
  LEFT JOIN MEASURETOOL MT  ON MT.MEASURETOOLID = sit.tools
  LEFT JOIN ARRANGEMENTS A  ON A.WORKFLOWSTEPID = '{0}' AND A.TOOLNO = MT.MEASURETOOLNAME AND A.CONTAINERID = '{1}' and A.STEPINFOID IS NOT NULL
 WHERE MT.MEASURETOOLID {2} ' ' AND s.workflowstepid = '{0}' AND A.ARRANGEMENTSID IS NULL
                            </string>.Value
        sql = String.Format(sql, stepDr("WorkflowstepID").ToString, stepDr("ContainerID").ToString, "<>")
        '查询出工步工装工具是否申请
        Dim dt = DatabaseHelper.Query_ReturnDatTable(sql)
        '如果存在没有申请的工步，则申请
        Dim dic As New Dictionary(Of String, String)
        If dt.Rows.Count > 0 Then
            For Each item As DataRow In dt.Rows
                dic.Clear()
                dic.Add("ContainerID", stepDr("ContainerID").ToString)
                dic.Add("QTY", item("QTY").ToString)
                dic.Add("ToolName", item("DESCRIPTION").ToString)
                dic.Add("ToolNo", item("TOOLNAME").ToString)
                dic.Add("ToolType", "LY14")
                dic.Add("EmployeeID", para("EmployeeID"))
                dic.Add("MfgOrderName", stepDr("MfgOrderName").ToString)
                dic.Add("TeamID", para("TeamID"))
                dic.Add("WorkflowstepID", stepDr("WorkflowstepID").ToString)
                dic.Add("ArrangementsID2", arrangementsID2)
                dic.Add("WorkflowID", stepDr("WorkflowID").ToString)
                dic.Add("ContainerType", stepDr("ContainerType").ToString)
                dic.Add("StepInfoID", item("StepInfoID").ToString)
                dic.Add("ApplyType", "0")
                sqlList.AddRange(GetAddPreparationInfoSql(dic))
            Next
        End If
        '查询工步辅料是否申请
        sql = <string>
SELECT sit.qty,sit.stepinfoid,sit.sequence, am.auxmaterialid,am.auxmaterialname,am.description,am.specification,am.vendormodel, am.notes
 FROM stepinfotools sit
LEFT JOIN stepinfo s  ON s.stepinfoid = sit.stepinfoid
inner join auxmaterial am on am.auxmaterialid = sit.tools
 LEFT JOIN ARRANGEMENTS A  ON A.WORKFLOWSTEPID ='{1}' AND A.TOOLNO = am.auxmaterialname AND A.CONTAINERID = '{0}' and A.STEPINFOID IS NOT NULL
WHERE s.workflowstepid = '{1}' AND A.Arrangementsid IS NULL
                  </string>.Value
        sql = String.Format(sql, stepDr("ContainerID").ToString, stepDr("WorkflowstepID").ToString)
        Dim dtaux = DatabaseHelper.Query_ReturnDatTable(sql)
        '如果存在未申请的辅料
        If dtaux.Rows.Count > 0 Then
            For Each item As DataRow In dtaux.Rows
                dic.Clear()
                dic.Add("ContainerID", stepDr("ContainerID").ToString)
                dic.Add("QTY", item("QTY").ToString)
                dic.Add("ToolName", item("DESCRIPTION").ToString)
                dic.Add("ToolNo", item("auxmaterialname").ToString)
                dic.Add("ToolType", "LY14")
                dic.Add("EmployeeID", para("EmployeeID"))
                dic.Add("MfgOrderName", stepDr("MfgOrderName").ToString)
                dic.Add("TeamID", para("TeamID"))
                dic.Add("WorkflowstepID", stepDr("WorkflowstepID").ToString)
                dic.Add("ArrangementsID2", arrangementsID2)
                dic.Add("WorkflowID", stepDr("WorkflowID").ToString)
                dic.Add("ContainerType", stepDr("ContainerType").ToString)
                dic.Add("StepInfoID", item("StepInfoID").ToString)
                dic.Add("ApplyType", "5")
                sqlList.AddRange(GetAddPreparationInfoSql(dic))
            Next
        End If
        Return sqlList
    End Function

    ''' <summary>
    ''' 保存申请生产准备
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetAddPreparationInfoSql(ByVal para As Dictionary(Of String, String)) As ArrayList
        Dim sqlList As New ArrayList
        Dim ID = Guid.NewGuid().ToString
        Dim strSQL As String = <string>
                    INSERT INTO ARRANGEMENTS
                      (CONTAINERID,QTY,TOOLNAME, TOOLNO, TOOLTYPE,TOOLSTATUS,CREATEDATE,ISINTEGRATION,
                        EMPLOYEEID, MFGORDERNAME, NEEDDATE, TEAMID,WORKFLOWSTEPID,ARRANGEMENTSID,ISTRANSFER,ARRANGEMENTSID2,WORKFLOWID,StepInfoID,ApplyType)
                    VALUES(</string>.Value
        strSQL += String.Format("'{0}',", para("ContainerID").ToString)
        strSQL += String.Format("'{0}',", para("QTY").ToString)
        strSQL += String.Format("'{0}',", para("ToolName").ToString)
        strSQL += String.Format("'{0}',", para("ToolNo").ToString)
        strSQL += String.Format("'{0}',", para("ToolType").ToString)
        strSQL += "'待准备',sysdate,'1',"
        strSQL += String.Format("'{0}',", para("EmployeeID"))
        strSQL += String.Format("'{0}',", para("MfgOrderName").ToString)
        strSQL += "sysdate,"
        strSQL += String.Format("'{0}',", para("TeamID"))
        strSQL += String.Format("'{0}',", para("WorkflowstepID").ToString)
        strSQL += String.Format("'{0}',", ID)
        strSQL += "0,"
        strSQL += String.Format("'{0}',", para("ArrangementsID2").ToString)
        strSQL += String.Format("'{0}',", para("WorkflowID").ToString)
        strSQL += String.Format("'{0}',", para("StepInfoID").ToString)
        strSQL += String.Format("'{0}')", para("ApplyType").ToString)
        sqlList.Add(strSQL)
        If para("ContainerType").ToString = "7" Then
            strSQL = <string>
                INSERT INTO ArrangementsContainer (ARRANGEMENTSID,CONTAINERID) VALUES ('{0}','{1}')
                 </string>.Value
            strSQL = String.Format(strSQL, ID, para("ContainerID").ToString)
            sqlList.Add(strSQL)
        End If
        Return sqlList
    End Function

#Region "转工排产"
    ''' <summary>
    ''' 获取转工排产下发列表数据（分页）
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetApsPlanShipListByPage(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim strSql As New StringBuilder
        strSql.AppendFormat(<string>SELECT Row_Number() OVER(ORDER BY cci.mfgorderid,cci.containerid,wfs.sequence) numer,
apd.apsplandetailid, cci.mfgorderid,cci.mfgordername,cci.containerid,cci.containername,cci.productid, cci.productdescription description,
cci.productname,cci.qty,wb.workflowname,wb.workflowname||':'||w.workflowrevision workflowinfo,w.workflowid,cci.containertype,res.resourcename,res.description resourcedesc,wm_concat(DISTINCT e.fullname) apsPlanEmpName,apd.qty apsqty,
DECODE(cci.containertype,'2','批量加工','3','批量子批次','7','单件单卡','') containertypename,apd.workflowstepid,apd.workflowstepname,s.specid,SUBSTR(wfs.workflowstepname,0,INSTR(wfs.workflowstepname,'-')-1)  specno,
to_char(apd.starttime,'yyyy-mm-dd hh24:mi:ss') starttime,to_char(apd.endtime,'yyyy-mm-dd hh24:mi:ss') endtime,
to_char(apd.ResStartTime,'yyyy-mm-dd hh24:mi:ss') ResStartTime,to_char(apd.ResEndTime,'yyyy-mm-dd hh24:mi:ss') ResEndTime,api.apstype,DECODE(api.apstype,'1','正排','2','倒排','3','瓶颈排','') apstypename,api.apsplaninfoid,apd.workcenterid,apd.teamid,apd.resourceid
FROM containercurrentinfo cci 
LEFT JOIN apsplaninfo api ON api.containerid = cci.containerid
LEFT JOIN apsplandetail apd ON apd.apsplaninfoid=api.apsplaninfoid
left join workflowstep wfs on wfs.workflowstepid=apd.workflowstepid
LEFT JOIN specbase sb ON sb.specbaseid = wfs.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,wfs.specid)
LEFT JOIN resourcedef res ON res.resourceid = apd.resourceid
LEFT JOIN Workflow w ON w.workflowid = api.workflowid
LEFT JOIN workflowbase wb ON wb.workflowbaseid = w.workflowbaseid 
LEFT JOIN apsplandetailemp ade ON ade.apsplandetailid = apd.apsplandetailid
LEFT JOIN employee e ON e.employeeid = ade.employeeid
WHERE cci.originalfactoryid != '{0}' AND cci.isinpool=1 AND cci.containerstatus=1 AND cci.isinstore!=1  
AND api.apstype2=2 AND api.isuse = 0 AND apd.isuse = 0 AND apd.shipreceivedate IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM containerstepworkreport csr WHERE csr.containerid = apd.containerid and csr.workflowstepid = apd.workflowstepid and csr.isuse = 0)  
AND apd.SpecFactoryID = '{0}' AND NVL(NVL(wfs.controlcode,s.controlcode),'0') != 'LY05'</string>.Value, para("factory").ToString)

        If Not String.IsNullOrWhiteSpace(para("mfgOrderName").ToString) Then
            strSql.AppendFormat(" AND cci.mfgordername LIKE '%{0}%'", para("mfgOrderName").ToString)
        End If

        If Not String.IsNullOrWhiteSpace(para("containerName").ToString) Then
            strSql.AppendFormat(" AND cci.containername LIKE '%{0}%'", para("containerName").ToString)
        End If

        If Not String.IsNullOrWhiteSpace(para("productName").ToString) Then
            strSql.AppendFormat(" AND cci.productname LIKE '%{0}%'", para("productName").ToString)
        End If

        If Not String.IsNullOrWhiteSpace(para("specNo").ToString) Then
            strSql.AppendFormat(" AND wfs.workflowstepname LIKE '%{0}%'", para("specNo").ToString)
        End If

        If Not String.IsNullOrWhiteSpace(para("startTime1").ToString) Then
            strSql.AppendFormat(" AND apd.starttime >= to_date('{0}','yyyy-MM-dd hh24:mi:ss')", para("startTime1").ToString)
        End If

        If Not String.IsNullOrWhiteSpace(para("startTime2").ToString) Then
            strSql.AppendFormat(" AND apd.starttime <= to_date('{0}','yyyy-MM-dd hh24:mi:ss')", para("startTime2").ToString)
        End If

        If Not String.IsNullOrWhiteSpace(para("endTime1").ToString) Then
            strSql.AppendFormat(" AND apd.endtime >= to_date('{0}','yyyy-MM-dd hh24:mi:ss')", para("endTime1").ToString)
        End If

        If Not String.IsNullOrWhiteSpace(para("endTime2").ToString) Then
            strSql.AppendFormat(" AND apd.endtime <= to_date('{0}','yyyy-MM-dd hh24:mi:ss')", para("endTime2").ToString)
        End If

        If Not String.IsNullOrWhiteSpace(para("apsType").ToString) Then
            strSql.AppendFormat(" AND api.apstype = '{0}'", para("apsType").ToString)
        End If

        strSql.AppendFormat(" AND apd.status = '{0}'", para("status").ToString)

        If Not String.IsNullOrWhiteSpace(para("workCenter").ToString) Then
            strSql.AppendFormat(" AND apd.workcenterid = '{0}'", para("workCenter").ToString)
        End If

        If Not String.IsNullOrWhiteSpace(para("team").ToString) Then
            strSql.AppendFormat(" AND apd.teamid = '{0}'", para("team").ToString)
        End If
        strSql.AppendLine(<string>  
                GROUP BY apd.apsplandetailid, cci.mfgorderid,cci.mfgordername,cci.containerid,cci.containername,cci.productid, cci.productdescription,
                cci.productname,cci.qty,wb.workflowname,wb.workflowname,w.workflowrevision,w.workflowid,cci.containertype,res.description,apd.qty,
                apd.workflowstepid,apd.workflowstepname,wfs.workflowstepname,s.specid,res.resourcename,wfs.sequence,
                apd.starttime,apd.endtime,api.apstype,api.apsplaninfoid,apd.workcenterid,apd.teamid,apd.resendtime,apd.resstarttime,apd.resourceid</string>.Value)
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("pageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("pageSize").ToString)
        paraPage.strSQL = strSql.ToString()
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 保存转工排产下发
    ''' </summary>
    ''' <param name="dt">需要下发的排产信息</param>
    ''' <param name="param">页面填写的设备人员信息</param>
    ''' <param name="user">当前用户</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveApsPlanShipSendData(ByVal dt As DataTable, ByVal param As Dictionary(Of String, Object), ByVal user As CurrentUserInfo) As uMESProcessResult
        Dim result = New uMESProcessResult()
        Try
            Dim sqlList As New ArrayList
            Dim dic As New Dictionary(Of String, String)
            dic.Add("FactoryName", param("FactoryName").ToString())
            result = IsSend(dt, dic)
            '验证无误，则下发并派工
            If result.Result Then
                Dim disOperator As DataTable = param("operator") '操作工
                Dim idsCondition = String.Empty
                For Each dr As DataRow In dt.Rows
                    idsCondition += String.Format("'{0}',", dr("ApsPlanDetailID").ToString)
                Next
                idsCondition = idsCondition.TrimEnd(",")

                '将排产明细表状态更改为下发
                Dim updateApsDetail = String.Format("UPDATE apsplandetail ad SET ad.outemp = '{0}',ad.outdate = sysdate,ad.lastchangedate = sysdate,ad.status = '1' WHERE ad.apsplandetailid IN ({1})", user.EmployeeID, idsCondition)
                If Not String.IsNullOrWhiteSpace(param("resource").ToString()) Then
                    updateApsDetail = String.Format("UPDATE apsplandetail ad SET ad.outemp = '{0}',ad.resourceid = '{1}',ad.outdate = sysdate,ad.lastchangedate = sysdate,ad.status = '1' WHERE ad.apsplandetailid IN ({2})", user.EmployeeID, param("resource").ToString(), idsCondition)
                End If
                sqlList.Add(updateApsDetail)
                '如果选择了操作工，则更新排产人员信息
                If disOperator.Rows.Count > 0 Then
                    sqlList.Add(String.Format("delete from apsplandetailemp ade WHERE ade.apsplandetailid IN ({0})", idsCondition))
                    For Each dr As DataRow In dt.Rows
                        For Each em As DataRow In disOperator.Rows
                            sqlList.Add(String.Format("INSERT INTO apsplandetailemp(apsplandetailempid,apsplandetailid,employeeid,isuse) VALUES('{0}','{1}','{2}','0')", Guid.NewGuid.ToString(), dr("ApsPlanDetailID").ToString(), em("EmployeeID").ToString()))
                        Next
                    Next
                End If

                Dim apsSql = String.Format(<string>select distinct c.qty,ad.qty apsqty,w.factoryid wffactoryid,c.workflowstepid cuworkflowstepid,ad.* 
                                    from ApsPlanDetail ad 
                                    left join containercurrentinfo c on c.containerid=ad.containerid 
                                    left join ApsPlanInfo ai on ai.ApsPlanInfoID=ad.ApsPlanInfoID
                                    left join workflow w on w.workflowid=ai.workflowid
                                    left join workflowstep ws on ws.workflowstepid=ad.workflowstepid
                                    where w.factoryid != ad.SpecFactoryID and ad.apsplandetailid IN ({0}) and ad.isuse=0 
                                    order by ad.containerid,ad.workflowstepid</string>.Value, idsCondition)
                Dim apsDetailDt = DatabaseHelper.Query_ReturnDatTable(apsSql) '所有排产的记录
                '如果存在不是已接收的排产数据，则提示并返回
                If apsDetailDt.AsEnumerable().Where(Function(p) p("status").ToString <> "3").Count > 0 Then
                    result.Result = False
                    result.Message = "数据已过期，请重新查询"
                    Return result
                    Exit Function
                End If

                Dim apsEmpSql = String.Format("select * from ApsPlanDetailEmp ad where ad.apsplandetailid IN ({0})", idsCondition) '排产的人员
                Dim apsEmpDt = DatabaseHelper.Query_ReturnDatTable(apsEmpSql) '所有排产的人员

                '派工相关sql
                Dim selectDispatchSql = <string>select distinct csd.* from containerstepdispatch csd 
                                                    left join apsplandetail ad on csd.containerid=ad.containerid and csd.workflowstepid=ad.workflowstepid 
                                                    where csd.isuse=0 and ad.apsplandetailid IN ({0})</string>.Value
                Dim apsDispatchDt = DatabaseHelper.Query_ReturnDatTable(String.Format(selectDispatchSql, idsCondition)) '已经派工的数据

                '插入派工SQL
                Dim dispatchSql = <string>INSERT INTO CONTAINERSTEPDISPATCH(SourceType,CONTAINERID,FACTORYID,WORKCENTERID,TEAMID,WORKFLOWSTEPID,RESOURCEID,WORKERID,QTY,PLANNEDSTARTDATE,PLANNEDFINISHDATE,CONTAINERSTEPDISPATCHID,EMPLOYEEID,CREATEDATE,ISUSE,ISTRANSFER,PRIORITYCODE,STEPINFOID)
                                          VALUES(1,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',to_date('{8}','yyyy/mm/dd hh24:mi:ss'),to_date('{9}','yyyy/mm/dd hh24:mi:ss'),'{10}','{11}',sysdate,0,0,'{12}','{13}')</string>.Value
                '插入派工明细SQL
                Dim dispatchEmpSql = <string>begin delete CONTAINERSTEPDISPATCHDETAIL where CONTAINERSTEPDISPATCHID='{0}' and WORKERID='{1}';
                                                INSERT INTO CONTAINERSTEPDISPATCHDETAIL(CONTAINERSTEPDISPATCHID,WORKERID,CreateDate) VALUES('{0}','{1}',sysdate); end;</string>.Value
                '更新批次总表
                Dim containerDispatchSql = "UPDATE containercurrentinfo cu SET cu.dispatchnum = 1,cu.isdispatch ={0} where cu.containerid='{1}'"
                '查询设备人员表
                Dim resEmpSql = "SELECT el.resourcelistid resourceid,el.employeeid FROM employeeresourcelist el where el.resourcelistid='{0}'"

                Dim apsContainerDt As DataTable = apsDetailDt.DefaultView.ToTable(True, "containerid")
                For Each containerRow In apsContainerDt.Rows
                    Dim _apsDetailDt = apsDetailDt.Select("containerid='" + containerRow("containerid").ToString + "'")
                    Dim isCludeCurrent = False '自动派工中是否包含当前序
                    For Each row As DataRow In _apsDetailDt
                        If apsDispatchDt.Select(String.Format("containerid='{0}' and workflowstepid='{1}'", row("ContainerID").ToString, row("WORKFLOWSTEPID").ToString)).Length > 0 Then '已有派工记录
                            Continue For '排除已派工的数据
                        End If
                        If row("cuworkflowstepid") = row("WORKFLOWSTEPID") Then
                            isCludeCurrent = True
                        End If
                        Dim _apsEmpDrs = apsEmpDt.Select("ApsPlanDetailID='" + row("ApsPlanDetailID").ToString + "'")

                        Dim dispatchId = Guid.NewGuid.ToString

                        If String.IsNullOrWhiteSpace(row("RESOURCEID").ToString()) And String.IsNullOrWhiteSpace(param("resource").ToString()) Then
                            result.Result = False
                            result.Message = "请选择操作设备"
                            Return result
                            Exit Function
                        End If
                        If _apsEmpDrs.Length = 0 And disOperator.Rows.Count = 0 Then
                            result.Result = False
                            result.Message = "请选择操作人员"
                            Return result
                            Exit Function
                        End If
                        If Not String.IsNullOrWhiteSpace(param("resource").ToString()) Then
                            row("RESOURCEID") = param("resource").ToString()
                        End If
                        If Not String.IsNullOrWhiteSpace(param("workcenter").ToString()) Then
                            row("WORKCENTERID") = param("workcenter").ToString()
                        End If
                        If Not String.IsNullOrWhiteSpace(param("team").ToString()) Then
                            row("TEAMID") = param("team").ToString()
                        End If

                        '添加派工
                        sqlList.Add(String.Format(dispatchSql, row("ContainerID"), row("SpecFactoryID"), row("WORKCENTERID"), row("TEAMID"), row("WORKFLOWSTEPID"), row("RESOURCEID"), "",
                                                  row("apsqty").ToString, row("starttime").ToString, row("endtime").ToString, dispatchId, user.EmployeeID, "1", ""))

                        '添加派工明细
                        If disOperator.Rows.Count > 0 Then
                            For Each em As DataRow In disOperator.Rows
                                sqlList.Add(String.Format(dispatchEmpSql, dispatchId, em("EmployeeID").ToString()))
                            Next
                        Else
                            For Each apsEmpRow As DataRow In _apsEmpDrs
                                sqlList.Add(String.Format(dispatchEmpSql, dispatchId, apsEmpRow("EmployeeID").ToString))
                            Next
                        End If
                    Next
                    If isCludeCurrent Then
                        sqlList.Add(String.Format(containerDispatchSql, 1, containerRow("containerid").ToString))
                    Else
                        sqlList.Add(String.Format(containerDispatchSql, 0, containerRow("containerid").ToString))
                    End If
                Next
                DatabaseHelper.ExecuteSqlListTran(sqlList)
                result.Result = True
                result.Message = "下发成功"
            End If
        Catch ex As Exception
            result.Result = False
            result.Message = "下发失败，原因：" + ex.Message
        End Try
        Return result
    End Function

    ''' <summary>
    ''' 保存转工排产回收
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <param name="user"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveApsPlanShipRecallData(ByVal dt As DataTable, ByVal user As CurrentUserInfo) As uMESProcessResult
        Dim result = New uMESProcessResult()
        Try
            Dim sqlList As New ArrayList
            Dim strSql As New StringBuilder
            result = VaildRecall(dt) '验证回收
            If Not result.Result Then
                Return result
                Exit Function
            End If
            For Each dr As DataRow In dt.Rows
                strSql.Clear()
                strSql.AppendFormat("UPDATE apsplandetail ap SET ap.recycleemp = '{0}',ap.recycledate = sysdate,ap.lastchangedate = sysdate,ap.status = '3'", user.EmployeeID)
                strSql.AppendFormat(" WHERE ap.apsplandetailid = '{0}'", dr("apsPlanDetailId"))
                sqlList.Add(strSql.ToString)
            Next
            '删除派工记录
            Dim dtTemp As DataTable = dt.Clone
            Dim failflag As Boolean = False
            For Each dr As DataRow In dt.Rows
                dtTemp.Rows.Clear()
                dtTemp.Rows.Add(dr.ItemArray)
                'If Not ApsRecallDispatchStepinfo(dt) Then
                '    failflag = True
                'End If
                If Not ApsRecallDispatch(dt) Then
                    failflag = True
                End If
            Next
            If failflag Then
                result.Message = "删除派工出错"
                result.Result = False
                Return result
                Exit Function
            End If
            DatabaseHelper.ExecuteSqlTran(sqlList)
            result.Message = "回收成功"
            result.Result = True

        Catch ex As Exception
            result.Result = False
            result.Message = "回收失败，原因：" + ex.Message
        End Try
        Return result
    End Function
#End Region

End Class
